Use DBCC HELP() To get the Syntax information of documented and undocumented DBCC Commands..

We are very familiar with the DBCC commands. But sometimes we forget their syntax information. Microsoft provides DBCC Help command in SQL Server which very few of us know . So, the next time when you are using DBCC command and you are confused with the syntax information just use DBCC HELP and it will give you all Syntax information.

Syntax for DBCC Help command is as follows:

DBCC HELP ('dbcc_statement' | @dbcc_statement_var | '?') [ WITH NO_INFOMSGS ]

dbcc_statement @dbcc_statement_var: Is the name of the DBCC command for which to receive syntax information. Provide only the part of the DBCC command that follows DBCC, for example, CHECKDB instead of DBCC CHECKDB.

? : Returns all DBCC commands for which Help is available.

WITH NO_INFOMSGS : Suppresses all informational messages that have severity levels from 0 through 10.

Suppose we  want to know the syntax of DBCC CHECKDB. Then execute the below command:

DBCC HELP (CHECKDB);

GO

It will give you result as below:

dbcc CHECKDB

(

{ 'database_name' | database_id | 0 }

[ , NOINDEX

| { REPAIR_ALLOW_DATA_LOSS

| REPAIR_FAST

| REPAIR_REBUILD

} ]

)

[ WITH

{

[ ALL_ERRORMSGS ]

[ , [ NO_INFOMSGS ] ]

[ , [ TABLOCK ] ]

[ , [ ESTIMATEONLY ] ]

[ , [ PHYSICAL_ONLY ] ]

[ , [ DATA_PURITY ] ]

[ , [ EXTENDED_LOGICAL_CHECKS ] ]

}

]

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This way we  can see the syntax information.

If you will use '?' with DBCC Help it will returns all DBCC commands as below for which the help is available.

DBCC HELP('?')

GO

OUTPUT: checkalloc, checkcatalog, checkconstraints, checkdb, checkfilegroup, checkident,

checktable etc.....

But from the output we can see there are many DBCC commands which are not displayed in this result set. Like Page,addinstance, auditevent, autopilot etc.

Lets execute the DBCC help for the syntax of DBCC Page.

DBCC HELP(Page)

GO

Output: Msg 8987, Level 16, State 1, Line 1 No help available for DBCC statement 'Page'.

So what do you think SQL don't have any DBCC Page command. No its not like that. Its giving error because DBCC Page and other commands which were missing from previous results are the undocumented commands of microsoft Sql server.

To get the help for these undocumented commands you need to turn on the trace flag 2588. As DBCC Traceon(2588). It will turn the flag ON only for that session. If you want to turn it ON globally then you can execute this as DBCC Traceon(2588,-1).

So lets turn on this trace flag and then we'll execute DBCC Help(Page) to see the result.

DBCC Traceon(2588)

Go

DBCC Help(Page)

Go

Output:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

dbcc Page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Yeahhhhh!!!! Now its giving syntax information for undocumented commands.

Now if you will execute the DBCC Help('?'). It will displays all the DBCC commands documented as well as undocumented.

DBCC Traceon(2588)

Go

DBCC Help('?')

Go

You can see the results in below screen shot.

Thanks For Reading this Blog!!!!!

References: Microsoft BOL