Undocumented Commands

Note:-All the samples here are run against AdventureWorks sample DB

DBCC proccache

Provides procuedure cache information

DBCC proccache

dbcc showfilestats

It shows total size and used size in extents.
If you multiply it by 64 you will get size in KB.

dbcc showfilestats

DBCC cachestats

show cache information cache hit ratio , number of times cache is used and pages used.

DBCC cachestats

DBCC activecursors
DBCC activecursors [SPID]

Provide activecursors information for selected DBCC Commands

DBCC activecursors (59)

DBCC showtableaffinity

DBCC showtableaffinity ('person.address')

DBCC detachdb

It will detach database.

DBCC detachdb ('db name')

sp_msforeachtable is will execute the command against every table in the database.

Exec sp_msforeachtable 'select convert(varchar(512),''?''),count(*) from ?'


sp_MSindexspace tablename , index_name

Will provide information of index_id, index_name, size, additional comment for all index for selected table.

exec sp_msindexspace '[sales].[storecontact]'


sp_MStablespace tablename

Provide information for tables space

sp_mstablespace 'person.address'

Will run the command for each database in the server

exec sp_msforeachdb 'sp_spaceused'


sp_MShelpindex tablename , indexname , flags

sp_mshelpindex 'person.address'

Returns index name, status , index id, fillfactor , columns, fulltext key, order etc...


Provide information for columns of a table
sp_MShelpcolumns 'person.address'

Provides information about system data types and user data types.

exec sp_MShelptype

Provide free space information for each disk.

exec xp_fixeddrives


Post a Comment

Popular Posts