DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC 
CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC 
SHRINKFILE etc. But there are a whole load of DBCC commands 
which are very useful for DBAs.

DBCC SHOWCONTIG  - TO FIND THE FRAGMENTATION

DBCC SHRINKDATABASE - TO REDUCE THE SIZE OF THE DATABASE 

DBCc SHRINK FILE - TO REDUCE THE SIZE OF THE FILE

dbcc opentran,dbcc inputbuffer,dbcc outputbuffer.

DBCC commands

1.	DBCC CHECKCONSTRAINTS <db_name>|<indexname> WITH 
ALL_CONSTRAINTS 
Here with all coinstraints include disable coinstraints also

DBCC CHECKCONSTRAINTS checks the integrity of foreign key 
and checked constraints, but does not check the integrity 
of a table's on-disk data structures. These data structure 
checks can be performed with DBCC CHECKDB and DBCC 
CHECKTABLE

2. DBCC CHECKTABLE performs a physical consistency check on 
tables and indexed views. The NOINDEX option, used only for 
backward compatibility, also applies to indexed views.
For the specified table, DBCC CHECKTABLE checks that: 
·	Index and data pages are correctly linked.
·	Indexes are in their proper sort order.
·	Pointers are consistent.
·	The data on each page is reasonable.
·	Page offsets are reasonable.

DBCC CHECKTABLE does not acquire a table lock by default. 
Instead, it acquires a schema lock that prevents meta data 
changes but allows changes to the data.
To perform DBCC CHECKTABLE on every table in the database, 
use DBCC CHECKDB.

3. DBCC DBREINDEX can rebuild all of the indexes for a 
table in one statement, which is easier than coding 
multiple DROP INDEX and CREATE INDEX statements. Because 
the work is done by one statement, DBCC DBREINDEX is 
automatically atomic

4. DBCC HELP returns a result set displaying the syntax for 
the specified DBCC statement


5. DBCC INDEXDEFRAG can defragment clustered and 
nonclustered indexes on tables and views. DBCC INDEXDEFRAG 
defragments the leaf level of an index so that the physical 
order of the pages matches the left-to-right logical order 
of the leaf nodes, thus improving index-scanning 
performance. 
DBCC INDEXDEFRAG also compacts the pages of an index, 
taking into account the FILLFACTOR specified when the index 
was created. Any empty pages created as a result of this 
compaction will be removed