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
sp_msforeachtable is will execute the command against every table in the database.


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

sp_msindexspace

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

sp_MStablespace tablename

Provide information for tables space

sp_mstablespace 'person.address'

sp_msforeachdb
Will run the command for each database in the server

exec sp_msforeachdb 'sp_spaceused'

sp_MShelpindex

sp_MShelpindex tablename , indexname , flags

sp_mshelpindex 'person.address'

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

sp_MShelpcolumns

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

sp_MShelptype
Provides information about system data types and user data types.

exec sp_MShelptype

xp_fixeddrives
Provide free space information for each disk.

exec xp_fixeddrives

2 comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts