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
