The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for Microsoft® SQL Server™ 2000. These statements check the physical and logical consistency of a database. Many DBCC statements can fix detected problems.
Database Console Command statements are grouped into these categories.
| Statement category | Perform |
| Maintenance statements | Maintenance tasks on a database, index, or filegroup. |
| Miscellaneous statements | Miscellaneous tasks such as enabling row-level locking or removing a dynamic-link library (DLL) from memory. |
| Status statements | Status checks. |
| Validation statements | Validation operations on a database, table, index, catalog, filegroup, system tables, or allocation of database pages. |
The DBCC statements of SQL Server 2000 take input parameters and return values. All DBCC statement parameters can accept both Unicode and DBCS literals.
Using DBCC Result Set Outputs
Many DBCC commands can produce output in tabular form (using the WITH TABLERESULTS option). This information can be loaded into a table for further use. An example script is shown below:
-- Create the table to accept the results
CREATE TABLE #tracestatus (
TraceFlag INT,
Status INT
)
-- Execute the command, putting the results in the table
INSERT INTO #tracestatus
EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
-- Display the results
SELECT *
FROM #tracestatus
GO
Maintenance Statements
DBCC DBREINDEX
DBCC DBREPAIR
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
DBCC UPDATEUSAGE
Miscellaneous Statements
DBCC dllname (FREE)
DBCC HELP
DBCC PINTABLE
DBCC ROWLOCK
DBCC TRACEOFF
DBCC TRACEON
DBCC UNPINTABLE
Status Statements
DBCC INPUTBUFFER
DBCC OPENTRAN
DBCC OUTPUTBUFFER
DBCC PROCCACHE
DBCC SHOWCONTIG
DBCC SHOW_STATISTICS
DBCC SQLPERF
DBCC TRACESTATUS
DBCC USEROPTIONS
Validation Statements
DBCC CHECKALLOC
DBCC CHECKCATALOG
DBCC CHECKCONSTRAINTS
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKIDENT
DBCC CHECKTABLE
DBCC NEWALLOC
