llocdump( dbid, page ) bhash( { print_bufs | no_print }, bucket_limit ) buffer( [ dbid ][, objid ][, nbufs ], printopt={ 0 | 1 | 2 }, buftype ) bytes( startaddress, length ) checkalloc[( dbname [, fix | nofix ] ) ] checkcatalog[( dbname )] checkdb[( dbname [, skip_ncindex ] ) ] checktable( tablename | tabid [, skip_ncindex ] ) dbinfo( [ dbname ] ) dbrepair( dbid, option={ dropdb | fixindex | fixsysindex }, table, indexid ) dbtable( dbid ) delete_row( dbid, pageid, delete_by_row={ 1 | 0 }, rownum ) des( [ dbid ][, objid ] ) extentcheck( dbid, objid, indexid, sort={1|0} ) extentchain( dbid, objid, indexid, sort={1|0},display(1,0) ) extentdump( dbid, page ) extentzap( dbid, objid, indexid, sort ) findnotfullextents( dbid, objid, indexid, sort={ 1 | 0 } ) fix_al( [ dbname ] ) help( dbcc_command ) ind( dbid, objid, printopt={ 0 | 1 | 2 } ) indexalloc(tablename|tabid, indid, [full | optimized | fast],[fix | nofix]) locateindexpgs( dbid, objid, page, indexid, level ) lock log( [dbid][,objid][,page][,row][,nrecords][,type={-1..36}],printopt={0|1} ) memusage netmemshow( option={1 | 2 | 3} ) netmemusage newalloc( dbname, option={ 1 | 2 | 3 } ) page( dbid, pagenum [, printopt={0|1|2} ][, cache={0|1} ][, logical={1|0} ] ) pglinkage( dbid, start, number, printopt={0|1|2}, target, order={1|0} ) pktmemshow( option={spid} ) procbuf( dbid, objid, nbufs, printopt={ 0 | 1 } ) prtipage( dbid, objid, indexid, indexpage ) pss( suid, spid, printopt={ 1 | 0 } ) rebuildextents( dbid, objid, indexid ) resource show_bucket( dbid, pageid, lookup_type ) tab( dbid, objid, printopt={ 0 | 1 | 2 } ) tablealloc(tablename|tabid, [full | optimized | fast],[fix | nofix]) traceoff( tracenum [, tracenum ... ] ) traceon( tracenum [, tracenum ... ] ) undo( dbid, pageno, rowno ) －－－－－－－－－－－－－－－－－－－－－－－－－－－－－ Introduction Undocumented DBCC commands: DBCC BUFFER DBCC BYTES DBCC DBINFO DBCC DBTABLE DBCC DES DBCC HELP DBCC IND DBCC LOG DBCC PAGE DBCC PROCBUF DBCC PRTIPAGE DBCC PSS DBCC RESOURCE DBCC TAB Literature -------------------------------------------------------------------------------- Introduction In this article I want to tell you about some useful undocumented DBCC commands, and how you can use these commands in SQL Server 7.0 for administering and monitoring.DBCC is an abbreviation of a DataBase Consistency Checker. This is the description of DBCC from SQL Server Books Online:A statement used to check the logical and physical consistency of a database, check memory usage, decrease the size of a database, check performance statistics, and so on. Database consistency checker (DBCC) ensures the physical and logical consistency of a database, but is not corrective.
Undocumented DBCC commands 1. DBCC BUFFER This command can be used to print buffer headers and pages from the buffer cache. Syntax:dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt]) where dbid|dbname - database id|database name. objid|objname - object id|object name nbufs - number of buffers to examine printopt - print option 0 - print out only the buffer header and page header (default) 1 - print out each row separately and the offset table 2 - print out each row as a whole and the offset tableThis is the example:DBCC TRACEON (3604) dbcc buffer(pubs,'sysobjects') 2. DBCC BYTES This command can be used to dump out bytes from a specific address. Syntax:dbcc bytes ( startaddress, length ) where startaddress - starting address to dump length - number of bytes to dumpThis is the example:DBCC TRACEON (3604) dbcc bytes (1000000, 100) 3. DBCC DBINFO Print DBINFO structure for specified database.DBCC DBINFO [( dbname )] where dbname - is the database name.This is the example:DBCC TRACEON (3604) DBCC DBINFO (pubs) 4. DBCC DBTABLE This command prints out the contents of the DBTABLE structure. Syntax:DBCC DBTABLE ({dbid|dbname}) where dbid|dbname - database name or database IDThis is the example:DBCC TRACEON (3604) DBCC DBTABLE (pubs) The DBTABLE structure has an output parameter called dbt_open. This parameter keeps track of how many users are in the database.Look at here for more details: FIX: Database Usage Count Does Not Return to Zero5. DBCC DES Prints the contents of the specified DES (descriptor). Syntax:dbcc des [( [dbid|dbname] [,objid|objname] )] where dbid|dbname - database id|database name. objid|objname - object id|object nameThis is the example:DBCC TRACEON (3604) DBCC DES 6. DBCC HELP DBCC HELP returns syntax information for the specified DBCC statement. In comparison with DBCC HELP command in version 6.5, it returns syntax information only for the documented DBCC commands.Syntax:DBCC HELP ('dbcc_statement' | @dbcc_statement_var | '?') This is the example:DBCC TRACEON (3604) DECLARE @dbcc_stmt sysname SELECT @dbcc_stmt = 'CHECKTABLE' DBCC HELP (@dbcc_stmt) Look at here for more details: DBCC HELP (T-SQL)7. DBCC IND Shows all pages in use by indexes of the specified table. Syntax:dbcc ind( dbid|dbname, objid|objname, [printopt = { 0 | 1 | 2 }] ) where dbid|dbname - database id|database name. objid|objname - object id|object name printopt - print optionThis is the example:DBCC TRACEON (3604) DBCC IND (pubs, authors) 8. DBCC log This command is used to view the transactional log for the specified database.DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] ) PARAMETERS: Dbid or dbname - Enter either the dbid or the name of the database in question. type - is the type of output: 0 - minimum information (operation, context, transaction id) 1 - more information (plus flags, tags, row length) 2 - very detailed information (plus object name, index name, page id, slot id) 3 - full information about each operation 4 - full information about each operation plus hexadecimal dump of the current transaction log's row. -1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XACTIDby default type = 0To view the transaction log for the master database, you can run the following command:DBCC log (master) 9. DBCC PAGE You can use this command to view the data page structure.DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical]) PARAMETERS: Dbid or dbname - Enter either the dbid or the name of the database in question. Pagenum - Enter the page number of the SQL Server page that is to be examined. Print option - (Optional) Print option can be either 0, 1, or 2. 0 - (Default) This option causes DBCC PAGE to print out only the page header information. 1 - This option causes DBCC PAGE to print out the page header information, each row of information from the page, and the page's offset table. Each of the rows printed out will be separated from each other. 2 - This option is the same as option 1, except it prints the page rows as a single block of information rather than separating the individual rows. The offset and header will also be displayed.
Cache - (Optional) This parameter allows either a 1 or a 0 to be entered. 0 - This option causes DBCC PAGE to retrieve the page number from disk rather than checking to see if it is in cache. 1 - (Default) This option takes the page from cache if it is in cache rather than getting it from disk only. Logical - (Optional) This parameter is for use if the page number that is to be retrieved is a virtual page rather then a logical page. It can be either 0 or 1. 0 - If the page is to be a virtual page number. 1 - (Default) If the page is the logical page number.In this example one data page is viewed from the table titleauthor, database pubs.USE pubs GO DBCC TRACEON (3604) GO DECLARE @pgid int SELECT @pgid = first FROM sysindexes WHERE id = object_id('titleauthor') AND indid = 1 DBCC PAGE (pubs, @pgid, 1) GO This is the result from my computer:...DATA: Offset 32 - 011e9820: 04042000 3137322d 33322d31 31373650 .. .172-32-1176P 011e9830: 53333333 33016400 0000051a 16150f04 S3333.d......... ... Look at here for more details: Data page structure in MS SQL 6.510. DBCC procbuf This command prints procedure buffer headers and proc-headers from the procedure cache.Syntax:DBCC procbuf( [dbid], [objid], [nbufs], [printopt = {0|1}] ) where dbid - database ID objid - object ID nbufs - number of buffers to print printopt - print option (0 print out only the proc buff and proc header (default) 1 print out proc buff, proc header and contents of buffer)This is the example:DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('master') SELECT @objectid = object_id('sp_help') DBCC procbuf(@dbid,@objectid,1,0) 11. DBCC prtipage This command prints the page number pointed to by each row on the specified index page.Syntax:DBCC prtipage( dbid, objid, indexid, indexpage ) where dbid - database ID objid - object ID indexid - index ID indexpage - the logical page number of the index page to dumpThis is the example:DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('pubs') SELECT @objectid = object_id('authors') DBCC prtipage(@dbid,@objectid,1,0) 12. DBCC pss This command shows info about processes currently connected to the dataserver. Structure is the source of data contained in the sysprocesses table.Syntax:DBCC pss( suid, spid, printopt = { 1 | 0 } ) where suid - server user ID spid - server process ID printopt - print option (0 standard output, 1 all open DES's and current sequence tree)This is the example:DBCC TRACEON (3604) dbcc pss 13. DBCC resource This command shows dataserver level RESOURCE, PERFMON and DS_CONFIG info. RESOURCE shows addresses of various data structures used by the server. PERFMON structure contains master..spt_monitor field info. DS_CONFIG structure contains master..syscurconfigs field info.Syntax:DBCC resource This is the example:DBCC TRACEON (3604) DBCC resource 14. DBCC TAB You can use the following undocumented command to view the data pages structure (in comparison with DBCC PAGE, this command will return information about all data pages for viewed table, not only for particular number)Syntax:DBCC tab (dbid, objid) where dbid - is the database id objid - is the table idThis is the example:DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('pubs') SELECT @objectid = object_id('authors') DBCC TAB (@dbid,@objectid)
Literature 1. "What are all the dbcc commands for SQL Server?" NTFAQ http://www.ntfaq.com/Articles/Index.cfm?ArticleID=141862. INFO: Description of DBCC PAGE Command http://support.microsoft.com/support/kb/articles/Q83/0/65.ASP3. THE UNAUTHORIZED DOCUMENTATION OF DBCC http://user.icx.net/~huntley/dbccinfo.htm4. The Totally Unauthorized List of Sybase DBCC Commands http://www.kaleidatech.com/dbcc1.htm5. The Totally Unauthorized List of Sybase DBCC Commands http://www.kaleidatech.com/dbcc2.htm6. The Totally Unauthorized List of Sybase DBCC Commands http://www.kaleidatech.com/dbcc3.htm7. FIX: Database Usage Count Does Not Return to Zero http://support.microsoft.com/support/kb/articles/Q175/3/03.ASP8. DBCC HELP (T-SQL) http://msdn.microsoft.com/library/psdk/sql/dbcc_10.htm
Useful Undocumented SQL Server Extended Stored Procedures **** sp_helpextendedproc ********by Alexander ChigrikAn extended stored procedure (xp) is a dynamic link library that runs directly in the address space of SQL Server and is programmed using the SQL Server Open Data Services API. You can run extended stored procedures from the Query Analyzer, for example, just as you would normal stored procedures. Extended stored procedures are used to extend the capabilities of SQL Server. You can take advantage of the many extended stored procedures that come with SQL Server, or you can can write your own in a programming language such as C or C++.In this article, I want to tell you about some useful undocumented extended stored procedures. These extended stored procedures work with SQL Server 7.0, as well as with SQL Server 2000.sp_MSgetversion This extended stored procedure can be used to get the current version of Microsoft SQL Server. To get the current SQL Server version, run:EXEC master..sp_MSgetversionNote. A more common way to retrieve the current SQL Server version (this way provides more information) is to use following SELECT statement:SELECT @@version xp_dirtree This extended stored procedure can be used to get a list of all the folders for the folder named in the xp. To get a list of all the folders in the C:\MSSQL7 folder, run:EXEC master..xp_dirtree 'C:\MSSQL7' xp_subdirs This extended stored procedure is used to get the list of folders for the folder named in the xp. In comparison with xp_dirtree, xp_subdirs returns only those directories whose depth = 1.This is the example:EXEC master..xp_subdirs 'C:\MSSQL7' xp_enum_oledb_providers This extended stored procedure is used to list of all the available OLE DB providers. It returns Provider Name, Parse Name and Provider Description. To get a list of all OLE DB providers for your SQL Server, run:EXEC master..xp_enum_oledb_providers xp_enumcodepages This extended stored procedure can be used to list of all code pages, character sets and their description for your SQL Server. To see this, list, run:EXEC master..xp_enumcodepages xp_enumdsn This extended stored procedure returns a list of all system DSNs and their descriptions. To get the list of system DSNs, run:EXEC master..xp_enumdsn xp_enumerrorlogs This extended stored procedure returns the list of all error logs with their last change date. To get the list of error logs, run:EXEC master..xp_enumerrorlogs xp_enumgroups This extended stored procedure returns the list of Windows NT groups and their description. To get the list of the Windows NT groups, run:EXEC master..xp_enumgroups xp_fileexist You can use this extended stored procedure to determine whether a particular file exists on the disk or not. The syntax for this xp is:EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]For example, to check whether the file boot.ini exists on disk c: or not, run:EXEC master..xp_fileexist 'c:\boot.ini' xp_fixeddrives This very useful extended stored procedure returns the list of all hard drives and the amount of free space in Mb for each hard drive. To see the list of drives, run:EXEC master..xp_fixeddrives xp_getnetname This extended stored procedure returns the WINS name of the SQL Server that you're connected to. To view the name, run:EXEC master..xp_getnetname xp_readerrorlog This extended stored procedure returns the content of the errorlog file. You can find the errorlog file in the C:\MSSQL7\Log directory, by default. To see the text of the errorlog file, run:EXEC master..xp_readerrorlog xp_regdeletekey This extended stored procedure will delete an entire key from the registry. You should use it very carefully. The syntax is:EXECUTE xp_regdeletekey [@rootkey=]'rootkey', [@key=]'key' For example, to delete the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:EXEC master..xp_regdeletekey @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test' xp_regdeletevalue This extended stored procedure will delete a particular value for a key in the registry. You should use it very carefully. The syntax is:EXECUTE xp_regdeletevalue [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name'For example, to delete the value 'TestValue' for the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:EXEC master..xp_regdeletevalue @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue' xp_regread This extended stored procedure is used to read from the registry. The syntax is:EXECUTE xp_regread [@rootkey=]'rootkey', [@key=]'key' [, [@value_name=]'value_name'] [, [@value=]@value OUTPUT] For example, to read into the variable @test from the value 'TestValue' from the key 'SOFTWARE\Test' from the 'HKEY_LOCAL_MACHINE', run:DECLARE @test varchar(20)EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue', @value=@test OUTPUTSELECT @test xp_regwrite This extended stored procedure is used to write to the registry. The syntax is:EXECUTE xp_regwrite [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name', [@type=]'type', [@value=]'value'For example, to write the variable 'Test' to the 'TestValue' value, key 'SOFTWARE\Test', 'HKEY_LOCAL_MACHINE', run:EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue', @type='REG_SZ', @value='Test' Keep in mind that these undocumented extended stored procedures are not officially supported by Microsoft, and that they may not be found in the next version of SQL Server. Published with the express written permission of the author. Copyright 2001 Alexander Chigrik.
bhash( { print_bufs | no_print }, bucket_limit )
buffer( [ dbid ][, objid ][, nbufs ], printopt={ 0 | 1 | 2 }, buftype )
bytes( startaddress, length )
checkalloc[( dbname [, fix | nofix ] ) ]
checkcatalog[( dbname )]
checkdb[( dbname [, skip_ncindex ] ) ]
checktable( tablename | tabid [, skip_ncindex ] )
dbinfo( [ dbname ] )
dbrepair( dbid, option={ dropdb | fixindex | fixsysindex }, table, indexid )
dbtable( dbid )
delete_row( dbid, pageid, delete_by_row={ 1 | 0 }, rownum )
des( [ dbid ][, objid ] )
extentcheck( dbid, objid, indexid, sort={1|0} )
extentchain( dbid, objid, indexid, sort={1|0},display(1,0) )
extentdump( dbid, page )
extentzap( dbid, objid, indexid, sort )
findnotfullextents( dbid, objid, indexid, sort={ 1 | 0 } )
fix_al( [ dbname ] )
help( dbcc_command )
ind( dbid, objid, printopt={ 0 | 1 | 2 } )
indexalloc(tablename|tabid, indid, [full | optimized | fast],[fix | nofix])
locateindexpgs( dbid, objid, page, indexid, level )
lock
log( [dbid][,objid][,page][,row][,nrecords][,type={-1..36}],printopt={0|1} )
memusage
netmemshow( option={1 | 2 | 3} )
netmemusage
newalloc( dbname, option={ 1 | 2 | 3 } )
page( dbid, pagenum [, printopt={0|1|2} ][, cache={0|1} ][, logical={1|0} ] )
pglinkage( dbid, start, number, printopt={0|1|2}, target, order={1|0} )
pktmemshow( option={spid} )
procbuf( dbid, objid, nbufs, printopt={ 0 | 1 } )
prtipage( dbid, objid, indexid, indexpage )
pss( suid, spid, printopt={ 1 | 0 } )
rebuildextents( dbid, objid, indexid )
resource
show_bucket( dbid, pageid, lookup_type )
tab( dbid, objid, printopt={ 0 | 1 | 2 } )
tablealloc(tablename|tabid, [full | optimized | fast],[fix | nofix])
traceoff( tracenum [, tracenum ... ] )
traceon( tracenum [, tracenum ... ] )
undo( dbid, pageno, rowno )
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Introduction
Undocumented DBCC commands:
DBCC BUFFER
DBCC BYTES
DBCC DBINFO
DBCC DBTABLE
DBCC DES
DBCC HELP
DBCC IND
DBCC LOG
DBCC PAGE
DBCC PROCBUF
DBCC PRTIPAGE
DBCC PSS
DBCC RESOURCE
DBCC TAB
Literature
--------------------------------------------------------------------------------
Introduction
In this article I want to tell you about some useful undocumented
DBCC commands, and how you can use these commands in SQL Server 7.0
for administering and monitoring.DBCC is an abbreviation of a DataBase Consistency Checker.
This is the description of DBCC from SQL Server Books Online:A statement used to check the logical and physical consistency of a
database, check memory usage, decrease the size of a database, check
performance statistics, and so on. Database consistency checker (DBCC)
ensures the physical and logical consistency of a database, but is not
corrective.
1. DBCC BUFFER This command can be used to print buffer headers and pages from the
buffer cache.
Syntax:dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])
where dbid|dbname - database id|database name.
objid|objname - object id|object name
nbufs - number of buffers to examine
printopt - print option
0 - print out only the buffer header and page header
(default)
1 - print out each row separately and the offset table
2 - print out each row as a whole and the offset tableThis is the example:DBCC TRACEON (3604)
dbcc buffer(pubs,'sysobjects')
2. DBCC BYTES This command can be used to dump out bytes from a specific address.
Syntax:dbcc bytes ( startaddress, length )
where startaddress - starting address to dump
length - number of bytes to dumpThis is the example:DBCC TRACEON (3604)
dbcc bytes (1000000, 100)
3. DBCC DBINFO Print DBINFO structure for specified database.DBCC DBINFO [( dbname )]
where
dbname - is the database name.This is the example:DBCC TRACEON (3604)
DBCC DBINFO (pubs)
4. DBCC DBTABLE This command prints out the contents of the DBTABLE structure.
Syntax:DBCC DBTABLE ({dbid|dbname})
where
dbid|dbname - database name or database IDThis is the example:DBCC TRACEON (3604)
DBCC DBTABLE (pubs)
The DBTABLE structure has an output parameter called dbt_open.
This parameter keeps track of how many users are in the database.Look at here for more details:
FIX:
Database Usage Count Does Not Return to Zero5. DBCC DES Prints the contents of the specified DES (descriptor).
Syntax:dbcc des [( [dbid|dbname] [,objid|objname] )]
where dbid|dbname - database id|database name.
objid|objname - object id|object nameThis is the example:DBCC TRACEON (3604)
DBCC DES
6. DBCC HELP DBCC HELP returns syntax information for the specified DBCC statement.
In comparison with DBCC HELP command in version 6.5, it returns syntax
information only for the documented DBCC commands.Syntax:DBCC HELP ('dbcc_statement' | @dbcc_statement_var | '?')
This is the example:DBCC TRACEON (3604)
DECLARE @dbcc_stmt sysname
SELECT @dbcc_stmt = 'CHECKTABLE'
DBCC HELP (@dbcc_stmt)
Look at here for more details:
DBCC HELP
(T-SQL)7. DBCC IND Shows all pages in use by indexes of the specified table.
Syntax:dbcc ind( dbid|dbname, objid|objname, [printopt = { 0 | 1 | 2 }] )
where
dbid|dbname - database id|database name.
objid|objname - object id|object name
printopt - print optionThis is the example:DBCC TRACEON (3604)
DBCC IND (pubs, authors)
8. DBCC log This command is used to view the transactional log for the specified
database.DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )
PARAMETERS:
Dbid or dbname - Enter either the dbid or the name of the database
in question. type - is the type of output: 0 - minimum information (operation, context, transaction id) 1 - more information (plus flags, tags, row length) 2 - very detailed information (plus object name, index name,
page id, slot id) 3 - full information about each operation 4 - full information about each operation plus hexadecimal dump
of the current transaction log's row. -1 - full information about each operation plus hexadecimal dump
of the current transaction log's row, plus Checkpoint Begin,
DB Version, Max XACTIDby default type = 0To view the transaction log for the master database, you can run the
following command:DBCC log (master)
9. DBCC PAGE You can use this command to view the data page structure.DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])
PARAMETERS:
Dbid or dbname - Enter either the dbid or the name of the database
in question. Pagenum - Enter the page number of the SQL Server page that is to
be examined. Print option - (Optional) Print option can be either 0, 1, or 2. 0 - (Default) This option causes DBCC PAGE to print
out only the page header information.
1 - This option causes DBCC PAGE to print out the
page header information, each row of information
from the page, and the page's offset table. Each
of the rows printed out will be separated from
each other.
2 - This option is the same as option 1, except it
prints the page rows as a single block of
information rather than separating the
individual rows. The offset and header will also
be displayed.
entered.
0 - This option causes DBCC PAGE to retrieve the page
number from disk rather than checking to see if it is
in cache.
1 - (Default) This option takes the page from cache if it
is in cache rather than getting it from disk only. Logical - (Optional) This parameter is for use if the page number
that is to be retrieved is a virtual page rather then a
logical page. It can be either 0 or 1. 0 - If the page is to be a virtual page number.
1 - (Default) If the page is the logical page number.In this example one data page is viewed from the table titleauthor,
database pubs.USE pubs
GO
DBCC TRACEON (3604)
GO
DECLARE @pgid int
SELECT @pgid = first FROM sysindexes WHERE
id = object_id('titleauthor') AND indid = 1
DBCC PAGE (pubs, @pgid, 1)
GO
This is the result from my computer:...DATA:
Offset 32 -
011e9820: 04042000 3137322d 33322d31 31373650 .. .172-32-1176P
011e9830: 53333333 33016400 0000051a 16150f04 S3333.d.........
...
Look at here for more details:
Data
page structure in MS SQL 6.510. DBCC procbuf This command prints procedure buffer headers and proc-headers from
the procedure cache.Syntax:DBCC procbuf( [dbid], [objid], [nbufs], [printopt = {0|1}] )
where
dbid - database ID
objid - object ID
nbufs - number of buffers to print
printopt - print option
(0 print out only the proc buff and proc header (default)
1 print out proc buff, proc header and contents of buffer)This is the example:DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('master')
SELECT @objectid = object_id('sp_help')
DBCC procbuf(@dbid,@objectid,1,0)
11. DBCC prtipage This command prints the page number pointed to by each row on the
specified index page.Syntax:DBCC prtipage( dbid, objid, indexid, indexpage )
where
dbid - database ID
objid - object ID
indexid - index ID
indexpage - the logical page number of the index page to dumpThis is the example:DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('pubs')
SELECT @objectid = object_id('authors')
DBCC prtipage(@dbid,@objectid,1,0)
12. DBCC pss This command shows info about processes currently connected to the
dataserver. Structure is the source of data contained in the sysprocesses
table.Syntax:DBCC pss( suid, spid, printopt = { 1 | 0 } )
where
suid - server user ID
spid - server process ID
printopt - print option
(0 standard output,
1 all open DES's and current sequence tree)This is the example:DBCC TRACEON (3604)
dbcc pss
13. DBCC resource This command shows dataserver level RESOURCE, PERFMON and DS_CONFIG
info. RESOURCE shows addresses of various data structures used by
the server. PERFMON structure contains master..spt_monitor
field info. DS_CONFIG structure contains master..syscurconfigs
field info.Syntax:DBCC resource
This is the example:DBCC TRACEON (3604)
DBCC resource
14. DBCC TAB You can use the following undocumented command to view the data
pages structure (in comparison with DBCC PAGE, this command will
return information about all data pages for viewed table, not
only for particular number)Syntax:DBCC tab (dbid, objid)
where
dbid - is the database id
objid - is the table idThis is the example:DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('pubs')
SELECT @objectid = object_id('authors')
DBCC TAB (@dbid,@objectid)
Literature
1. "What are all the dbcc commands for SQL Server?" NTFAQ
http://www.ntfaq.com/Articles/Index.cfm?ArticleID=141862. INFO: Description of DBCC PAGE Command
http://support.microsoft.com/support/kb/articles/Q83/0/65.ASP3. THE UNAUTHORIZED DOCUMENTATION OF DBCC
http://user.icx.net/~huntley/dbccinfo.htm4. The Totally Unauthorized List of Sybase DBCC Commands
http://www.kaleidatech.com/dbcc1.htm5. The Totally Unauthorized List of Sybase DBCC Commands
http://www.kaleidatech.com/dbcc2.htm6. The Totally Unauthorized List of Sybase DBCC Commands
http://www.kaleidatech.com/dbcc3.htm7. FIX: Database Usage Count Does Not Return to Zero
http://support.microsoft.com/support/kb/articles/Q175/3/03.ASP8. DBCC HELP (T-SQL)
http://msdn.microsoft.com/library/psdk/sql/dbcc_10.htm
Extended Stored Procedures **** sp_helpextendedproc ********by Alexander ChigrikAn extended stored procedure (xp) is a dynamic link library that runs directly in the address space of SQL Server and is programmed using the SQL Server Open Data Services API. You can run extended stored procedures from the Query Analyzer, for example, just as you would normal stored procedures. Extended stored procedures are used to extend the capabilities of SQL Server. You can take advantage of the many extended stored procedures that come with SQL Server, or you can can write your own in a programming language such as C or C++.In this article, I want to tell you about some useful undocumented extended stored procedures. These extended stored procedures work with SQL Server 7.0, as well as with SQL Server 2000.sp_MSgetversion
This extended stored procedure can be used to get the current version of Microsoft SQL Server. To get the current SQL Server version, run:EXEC master..sp_MSgetversionNote. A more common way to retrieve the current SQL Server version (this way provides more information) is to use following SELECT statement:SELECT @@version
xp_dirtree
This extended stored procedure can be used to get a list of all the folders for the folder named in the xp. To get a list of all the folders in the C:\MSSQL7 folder, run:EXEC master..xp_dirtree 'C:\MSSQL7'
xp_subdirs
This extended stored procedure is used to get the list of folders for the folder named in the xp. In comparison with xp_dirtree, xp_subdirs returns only those directories whose depth = 1.This is the example:EXEC master..xp_subdirs 'C:\MSSQL7'
xp_enum_oledb_providers
This extended stored procedure is used to list of all the available OLE DB providers. It returns Provider Name, Parse Name and Provider Description. To get a list of all OLE DB providers for your SQL Server, run:EXEC master..xp_enum_oledb_providers
xp_enumcodepages
This extended stored procedure can be used to list of all code pages, character sets and their description for your SQL Server. To see this, list, run:EXEC master..xp_enumcodepages
xp_enumdsn
This extended stored procedure returns a list of all system DSNs and their descriptions. To get the list of system DSNs, run:EXEC master..xp_enumdsn
xp_enumerrorlogs
This extended stored procedure returns the list of all error logs with their last change date. To get the list of error logs, run:EXEC master..xp_enumerrorlogs
xp_enumgroups
This extended stored procedure returns the list of Windows NT groups and their description. To get the list of the Windows NT groups, run:EXEC master..xp_enumgroups
xp_fileexist
You can use this extended stored procedure to determine whether a particular file exists on the disk or not. The syntax for this xp is:EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]For example, to check whether the file boot.ini exists on disk c: or not, run:EXEC master..xp_fileexist 'c:\boot.ini'
xp_fixeddrives
This very useful extended stored procedure returns the list of all hard drives and the amount of free space in Mb for each hard drive. To see the list of drives, run:EXEC master..xp_fixeddrives
xp_getnetname
This extended stored procedure returns the WINS name of the SQL Server that you're connected to. To view the name, run:EXEC master..xp_getnetname
xp_readerrorlog
This extended stored procedure returns the content of the errorlog file. You can find the errorlog file in the C:\MSSQL7\Log directory, by default. To see the text of the errorlog file, run:EXEC master..xp_readerrorlog
xp_regdeletekey
This extended stored procedure will delete an entire key from the registry. You should use it very carefully. The syntax is:EXECUTE xp_regdeletekey [@rootkey=]'rootkey', [@key=]'key' For example, to delete the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:EXEC master..xp_regdeletekey @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test'
xp_regdeletevalue
This extended stored procedure will delete a particular value for a key in the registry. You should use it very carefully. The syntax is:EXECUTE xp_regdeletevalue [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name'For example, to delete the value 'TestValue' for the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:EXEC master..xp_regdeletevalue @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue'
xp_regread
This extended stored procedure is used to read from the registry. The syntax is:EXECUTE xp_regread [@rootkey=]'rootkey', [@key=]'key' [, [@value_name=]'value_name'] [, [@value=]@value OUTPUT] For example, to read into the variable @test from the value 'TestValue' from the key 'SOFTWARE\Test' from the 'HKEY_LOCAL_MACHINE', run:DECLARE @test varchar(20)EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue', @value=@test OUTPUTSELECT @test
xp_regwrite
This extended stored procedure is used to write to the registry. The syntax is:EXECUTE xp_regwrite [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name', [@type=]'type', [@value=]'value'For example, to write the variable 'Test' to the 'TestValue' value, key 'SOFTWARE\Test', 'HKEY_LOCAL_MACHINE', run:EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue', @type='REG_SZ', @value='Test'
Keep in mind that these undocumented extended stored procedures are not officially supported by Microsoft, and that they may not be found in the next version of SQL Server.
Published with the express written permission of the author. Copyright 2001 Alexander Chigrik.
好人哪to:j9988(j9988)
服了，请问老大什么时候开个sql强化班，小弟一定第一个报名，呵呵