------------------------------------
-- Undocumented DBCC Commands
-- Last Modified: 2008-12-29
-- Disclaimer: Run at your own risk!
-- Author: Pawel Potasinski
------------------------------------

---------------------------------------------------------
-- How to get a help on documented DBCC commands
---------------------------------------------------------
DBCC HELP('?')
DBCC HELP('SQLPERF')

---------------------------------------------------------
-- How to get a help on undocumented DBCC commands
---------------------------------------------------------
-- 1) Enable flag 2588 on SQL Server 2005/2008
DBCC TRACEON(2588); -- 2520 on 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
IF OBJECT_ID('tempdb.dbo.#dbccs') IS NOT NULL
  DROP TABLE #dbccs;
GO
CREATE TABLE #dbccs
(
	[dbcc] nvarchar(4000) NOT NULL
);
GO
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 [dbcc];

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('addinstance') WITH NO_INFOMSGS
--DBCC HELP('auditevent') WITH NO_INFOMSGS
--DBCC HELP('autopilot') WITH NO_INFOMSGS
--DBCC HELP('buffer') 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('cleanpage') WITH NO_INFOMSGS
--DBCC HELP('cleantable') WITH NO_INFOMSGS
--DBCC HELP('clearspacecaches') WITH NO_INFOMSGS
--DBCC HELP('collectstats') 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('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('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('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 undocumented 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')