---------------------------------------------------------
-- How to get a help on docummented DBCC commands
---------------------------------------------------------
DBCC HELP('?')
DBCC
 HELP('SQLPERF')
 
---------------------------------------------------------
-- How to get a help on undocummented DBCC commands
---------------------------------------------------------
 
-- 1) Enable flag 2588
DBCC TRACEON(2588) -- 2520 in SQL Server 7.0/2000
 
-- 2) List all DBCC commands
DBCC HELP('?') WITH NO_INFOMSGS
 
-- 3) Save the results of step 2) to C:\dbcc.rpt
 
-- 4) Create temporary table and import records from C:\dbcc.rpt
CREATE TABLE #dbccs
(
[dbcc] nvarchar(4000)
)
DECLARE @bulkcmd varchar(8000)
SET @bulkcmd = 'BULK INSERT #dbccs
FROM N''C:\dbcc.rpt''
WITH
(
ROWTERMINATOR = '''
 + NCHAR(10) + NCHAR(13) + NCHAR(10) + ''',
DATAFILETYPE = ''widechar''
)'
EXEC(@bulkcmd)
 
-- 5) Run the following SELECT with results to text and drop temporary table
SELECT 'DBCC HELP(''' + [dbcc] + ''') WITH NO_INFOMSGS'
FROM #dbccs
ORDER BY 1
DROP TABLE #dbccs
 
-- 6) Copy all DBCC HELP commands from Results window and paste it below
-- or just uncomment given DBCC HELP commands given below
-- and then execute them all
 
--DBCC HELP('activecursors') WITH NO_INFOMSGS
--DBCC HELP('addextendedproc') WITH NO_INFOMSGS
--DBCC HELP('addinstance') WITH NO_INFOMSGS
--DBCC HELP('auditevent') WITH NO_INFOMSGS
--DBCC HELP('autopilot') WITH NO_INFOMSGS
--DBCC HELP('buffer') WITH NO_INFOMSGS
--DBCC HELP('bytes') WITH NO_INFOMSGS
--DBCC HELP('cacheprofile') WITH NO_INFOMSGS
--DBCC HELP('cachestats') WITH NO_INFOMSGS
--DBCC HELP('callfulltext') WITH NO_INFOMSGS
--DBCC HELP('checkalloc') WITH NO_INFOMSGS
--DBCC HELP('checkcatalog') WITH NO_INFOMSGS
--DBCC HELP('checkconstraints') WITH NO_INFOMSGS
--DBCC HELP('checkdb') WITH NO_INFOMSGS
--DBCC HELP('checkfilegroup') WITH NO_INFOMSGS
--DBCC HELP('checkident') WITH NO_INFOMSGS
--DBCC HELP('checkprimaryfile') WITH NO_INFOMSGS
--DBCC HELP('checktable') WITH NO_INFOMSGS
--DBCC HELP('cleantable') WITH NO_INFOMSGS
--DBCC HELP('clearspacecaches') WITH NO_INFOMSGS
--DBCC HELP('collectstats') WITH NO_INFOMSGS
--DBCC HELP('concurrencyviolation') WITH NO_INFOMSGS
--DBCC HELP('config') WITH NO_INFOMSGS
--DBCC HELP('cursorstats') WITH NO_INFOMSGS
--DBCC HELP('dbinfo') WITH NO_INFOMSGS
--DBCC HELP('dbrecover') WITH NO_INFOMSGS
--DBCC HELP('dbreindex') WITH NO_INFOMSGS
--DBCC HELP('dbreindexall') WITH NO_INFOMSGS
--DBCC HELP('dbrepair') WITH NO_INFOMSGS
--DBCC HELP('dbtable') WITH NO_INFOMSGS
--DBCC HELP('debugbreak') WITH NO_INFOMSGS
--DBCC HELP('deleteinstance') WITH NO_INFOMSGS
--DBCC HELP('detachdb') WITH NO_INFOMSGS
--DBCC HELP('dropcleanbuffers') WITH NO_INFOMSGS
--DBCC HELP('dropextendedproc') WITH NO_INFOMSGS
--DBCC HELP('dumptrigger') WITH NO_INFOMSGS
--DBCC HELP('errorlog') WITH NO_INFOMSGS
--DBCC HELP('extentinfo') WITH NO_INFOMSGS
--DBCC HELP('fileheader') WITH NO_INFOMSGS
--DBCC HELP('fixallocation') WITH NO_INFOMSGS
--DBCC HELP('flush') WITH NO_INFOMSGS
--DBCC HELP('flushprocindb') WITH NO_INFOMSGS
--DBCC HELP('forceghostcleanup') WITH NO_INFOMSGS
--DBCC HELP('free') WITH NO_INFOMSGS
--DBCC HELP('freeproccache') WITH NO_INFOMSGS
--DBCC HELP('freesessioncache') WITH NO_INFOMSGS
--DBCC HELP('freesystemcache') WITH NO_INFOMSGS
--DBCC HELP('freeze_io') WITH NO_INFOMSGS
--DBCC HELP('help') WITH NO_INFOMSGS
--DBCC HELP('icecapquery') WITH NO_INFOMSGS
--DBCC HELP('incrementinstance') WITH NO_INFOMSGS
--DBCC HELP('ind') WITH NO_INFOMSGS
--DBCC HELP('indexdefrag') WITH NO_INFOMSGS
--DBCC HELP('inputbuffer') WITH NO_INFOMSGS
--DBCC HELP('invalidate_textptr') WITH NO_INFOMSGS
--DBCC HELP('invalidate_textptr_objid') WITH NO_INFOMSGS
--DBCC HELP('latch') WITH NO_INFOMSGS
--DBCC HELP('lock') WITH NO_INFOMSGS
--DBCC HELP('log') WITH NO_INFOMSGS
--DBCC HELP('loginfo') WITH NO_INFOMSGS
--DBCC HELP('mapallocunit') WITH NO_INFOMSGS
--DBCC HELP('memobjlist') WITH NO_INFOMSGS
--DBCC HELP('memorymap') WITH NO_INFOMSGS
--DBCC HELP('memorystatus') WITH NO_INFOMSGS
--DBCC HELP('metadata') WITH NO_INFOMSGS
--DBCC HELP('movepage') WITH NO_INFOMSGS
--DBCC HELP('no_textptr') WITH NO_INFOMSGS
--DBCC HELP('opentran') WITH NO_INFOMSGS
--DBCC HELP('optimizer_whatif') WITH NO_INFOMSGS
--DBCC HELP('outputbuffer') WITH NO_INFOMSGS
--DBCC HELP('page') WITH NO_INFOMSGS
--DBCC HELP('perfmon') WITH NO_INFOMSGS
--DBCC HELP('persiststackhash') WITH NO_INFOMSGS
--DBCC HELP('pintable') WITH NO_INFOMSGS
--DBCC HELP('proccache') WITH NO_INFOMSGS
--DBCC HELP('prtipage') WITH NO_INFOMSGS
--DBCC HELP('readpage') WITH NO_INFOMSGS
--DBCC HELP('renamecolumn') WITH NO_INFOMSGS
--DBCC HELP('resource') WITH NO_INFOMSGS
--DBCC HELP('ruleoff') WITH NO_INFOMSGS
--DBCC HELP('ruleon') WITH NO_INFOMSGS
--DBCC HELP('semetadata') WITH NO_INFOMSGS
--DBCC HELP('setcpuweight') WITH NO_INFOMSGS
--DBCC HELP('setinstance') WITH NO_INFOMSGS
--DBCC HELP('setioweight') WITH NO_INFOMSGS
--DBCC HELP('show_statistics') WITH NO_INFOMSGS
--DBCC HELP('showcontig') WITH NO_INFOMSGS
--DBCC HELP('showdbaffinity') WITH NO_INFOMSGS
--DBCC HELP('showfilestats') WITH NO_INFOMSGS
--DBCC HELP('showoffrules') WITH NO_INFOMSGS
--DBCC HELP('showonrules') WITH NO_INFOMSGS
--DBCC HELP('showtableaffinity') WITH NO_INFOMSGS
--DBCC HELP('showtext') WITH NO_INFOMSGS
--DBCC HELP('showweights') WITH NO_INFOMSGS
--DBCC HELP('shrinkdatabase') WITH NO_INFOMSGS
--DBCC HELP('shrinkfile') WITH NO_INFOMSGS
--DBCC HELP('sqlmgrstats') WITH NO_INFOMSGS
--DBCC HELP('sqlperf') WITH NO_INFOMSGS
--DBCC HELP('stackdump') WITH NO_INFOMSGS
--DBCC HELP('tec') WITH NO_INFOMSGS
--DBCC HELP('thaw_io') WITH NO_INFOMSGS
--DBCC HELP('traceoff') WITH NO_INFOMSGS
--DBCC HELP('traceon') WITH NO_INFOMSGS
--DBCC HELP('tracestatus') WITH NO_INFOMSGS
--DBCC HELP('unpintable') WITH NO_INFOMSGS
--DBCC HELP('updateusage') WITH NO_INFOMSGS
--DBCC HELP('useplan') WITH NO_INFOMSGS
--DBCC HELP('useroptions') WITH NO_INFOMSGS
--DBCC HELP('writepage') WITH NO_INFOMSGS
 
-- 7) Run DBCC HELP commands and you're done!
 
-- 8) Turn off the flag
DBCC TRACEOFF(2588)
 
---------------------------------------------------------
-- Some undocummented DBCC commands
---------------------------------------------------------
 
----------------------------------------------------------
-- DBCC IND - list of all pages used by indexes in a table
----------------------------------------------------------
DBCC IND(N'AdventureWorks',N'Sales.SalesOrderHeader',-1)
 
-----------------------------------------------
-- DBCC PAGE - displays the data page structure
-----------------------------------------------
DECLARE @dbid int
SET @dbid = DB_ID(N'AdventureWorks')
DBCC
 TRACEON(3604)
DBCC PAGE(@dbid,1,4301,1) -- choose the page number properly (use DBCC IND?)
 
------------------------------------------------------------------
-- DBCC FLUSHPROCINDB - clear procedure cache in a single database
------------------------------------------------------------------
USE AdventureWorks
GO
-- Generate cached plan
EXEC dbo.uspGetManagerEmployees 3
GO
-- View procedure cache
DECLARE @dbid int
SET @dbid = DB_ID('AdventureWorks')
SELECT sql FROM sys.syscacheobjects
WHERE dbid = @dbid
AND objtype = N'Proc'
-- Clear procedure cache
DBCC FLUSHPROCINDB(@dbid)
-- View procedure cache again
SELECT sql FROM sys.syscacheobjects
WHERE dbid = @dbid
AND
 objtype = N'Proc'
 
--------------------------------------------------
-- DBCC LOG - view transaction log of the database
--------------------------------------------------
DBCC LOG(N'AdventureWorks',3)
 
---------------------------------------------------------------------
-- DBCC LOGINFO - display VLFs in the transaction log of the database
---------------------------------------------------------------------
DBCC LOGINFO(N'AdventureWorks')