DBCC

Transact-SQL Reference

Transact-SQL Reference

DBCC

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