Is checkdb and check all tables equivalent?

[former_member211576]

Hi experts,

It takes a few weeks to run checkdb in our database. Could I use check all tables in parallel instead? Is it equivalent?

[clas_hortien · Accepted solution]

Hi,

yes, checkdb contains a checktable for all tables and a check alloc, so if you run a DBCC CHECKALLOC and a DBCC CHECKTABLE for all tables, you should have the same result. You can use this script for the CHECKTABLE part:

DECLARE @tablename sysname

DECLARE @username sysname

DECLARE @cmd varchar(4000)

DECLARE crs CURSOR FOR

SELECT user_name(uid), name FROM sysobjects WHERE type IN ('U' ,'S') ORDER BY name

OPEN crs

FETCH NEXT FROM crs INTO @username, @tablename

WHILE (@@fetch_status = 0) BEGIN

PRINT convert(char(25),getdate()) + @username + '.' + @tablename

SET @cmd = 'DBCC checktable ([' +@username + '.' + @tablename +']) WITH tablock, no_infomsgs'

EXEC (@cmd)

FETCH NEXT FROM crs INTO @username, @tablename

END

PRINT 'FINISHED'

CLOSE crs

DEALLOCATE crs

You can change the select for the cursor for your needs.

Best regards

Clas

Edited by: Clas Hortien on Feb 14, 2011 8:48 AM