DBCC DBREINDEX
This statement is used to recreate the indexes for a particular table. This statement rebuilds indexes in a single step. It also assigns fresh pages to reduce internal and external fragmentation.
DBCC DBREPAIR
This statement is used to drop or delete a damaged database. However, this command is no longer available with Microsoft SQL Server 2005 and later versions of Microsoft SQL Server. Instead, it has been replaced by the DROP DATABASE Transact-SQL statement.
DBCC INDEXDEFRAG
This statement is used to defragment the clustered and secondary indexes associated with the particular table. The index defragmentation is carried out using the fill factor specified at the time of creation of indexes. While its operation is strikingly similar to that of DBCC DBREINDEX, unlike DBCC INDEXFRAG it does not allow new fill factor to be specified.
DBCC SHRINKDATABASE
This statement is used to reduce the size of a database. This statement reduces the physical size of the database log file. An alternate way to shrink a database is to use the commander ALTER DATABASE.
DBCC SHRINKFILE
This statement is used to reduce the size of a data file or log file of a particular database. The file could also be shrunk by using the SHRINKFILE attribute of the ALTER DATABASE command.
DBCC UPDATEUSAGE
This statement is used to correct inaccuracies in the page and row statistics in the views.
DBCC CLEANTABLE
This statement is used to remove spaces occupied by columns when they are removed. This feature is not available with Microsoft SQL Server 2000 and has been newly introduced in Microsoft SQL Server 2005
DBCC DROPCLEANBUFFERS
This statement is used to drop clean buffers from the buffer pool. This feature is available with Microsoft SQL Server 2000 and in Microsoft SQL Server 2005
DBCC FREEPROCCACHE
This statement is used to remove all elements from the procedure cache. This feature is not available with Microsoft SQL Server 2000 and has been newly introduced in Microsoft SQL Server 2005
DBCC INPUTBUFFER
This statement is used to display the last statement stored in the buffer.
DBCC OPENTRAN
This statement is used to display information about the oldest open transaction.
DBCC OUTPUTBUFFER
This statement is used to return the current value of the output buffer.
DBCC PROCCACHE
This statement is used to display information about procedure cache.
DBCC SHOWCONTIG
This statement is used to display fragmentation information
DBCC SHOW_STATISTICS
This statement is used to show current distribution statistics
DBCC SQLPERF
This statement is used to show transaction log statistics
DBCC TRACESTATUS
This statement is used to display status of trace flags
DBCC USEROPTIONS
This statement is used to return set as ACTIVE
DBCC CHECKALLOC
This statement is used to checks whether every extent allocated by the system has been allocated and whether there are extents that have not been allocated.
DBCC CHECKCATALOG
This statement is used to check for consistency between system tables in the system catalog. It does so through cross-referencing checks.
DBCC CHECKCONSTRAINTS
This statement is used to check integrity of specific constraints.
DBCC CHECKDB
This statement is used to check integrity and allocation of specific objects in a database. It also performs DBCC CHECKALLOC, DBCC CHECKTABLE and DBCC CHECKCATALOG in that particular order.
DBCC CHECKFILEGROUP
This statement is used to check allocation and structural integrity of tables.
DBCC CHECKIDENT
This statement is used to check identity value of specified table.†
DBCC CHECKTABLE
This statement is used to check the integrity of a table and all the pages and structures which comprise the table. Both physical and logical checks are performed in this case. However, a PHYSICAL ONLY option can be used to check for physical consistency alone.
DBCC NEWALLOC
DBCC NEWALLOC is almost similar to DBCC CHECKALLOC. This statement is not supported by recent versions.
DBCC dllname (FREE)
This statement is used to unload a particular stored procedure DLL from memory.
DBCC HELP
This statement is used to return syntax information.
DBCC PINTABLE
This statement is used to mark a particular table to be pinned.
DBCC ROWLOCK
This statement is used to enable Insert Row Locking (IRL) operations.
DBCC TRACEOFF
This statement is used to disable a trace flag.
DBCC TRACEON
This statement is used to turn on a specific trace flag.
DBCC UNPINTABLE
This statement is used to mark a table as unpinned. In an unpinned table, the table pages in the cache could be easily removed.