http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/10/20/consistency-checking-options-for-a-vldb.aspx
DBCC CHECKDB includes:
DBCC CHECKALLOC
DBCC CHECKCATALOG
DBCC CHECKTABLE
http://sqlmag.com/blog/unkillable-dbcc-checkdb
DBCC CHECKDB(‘DatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS
http://www.bpsoftware.com/Blog/post/2008/11/25/SQL-DBCC-CHECKTABLE-on-multiple-tables.aspx
— set options
SET NOCOUNT ON;
SET ANSI_PADDING ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @tablenames TABLE (TableName VARCHAR(255));
— populate table with tablenames
INSERT INTO @tablenames
SELECT name FROM sys.objects
— additional filters can be set to narrow table selection
WHERE ([type] = N’U’);
— Declare a cursor to loop through table
DECLARE tablenames INSENSITIVE SCROLL CURSOR
FOR SELECT TableName FROM @tablenames;
— Open the cursor.
OPEN tablenames
— declare variables
DECLARE @curtablename AS VARCHAR(255);
DECLARE @cmd AS VARCHAR(1024);
— Loop through all the tables
WHILE (1=1)
BEGIN
FETCH NEXT FROM tablenames INTO @curtablename;
IF @@fetch_status <> 0 BREAK;
— run the command
SET @cmd = ‘DBCC CHECKTABLE ([‘ + rtrim(@curtablename) + ‘]) WITH ALL_ERRORMSGS’;
EXEC (@cmd)
PRINT @cmd + CHAR(10) + CHAR(13);
END
— Close cursor
CLOSE tablenames;
DEALLOCATE tablenames;
http://www.mssqltips.com/sqlservertip/2399/minimize-performance-impact-of-sql-server-dbcc-checkdb/