SQL Server - DBCC checktable

Instead of using DBCC CHECKDB , which checks on consistency and allocation across the whole database, an alternative is DBCC CHECKTABLE , which focuses on integrity checks on tables .

This query iterates through every table and returns the  integrity check results.

The DBCC CHECKTABLE results are inserted to a #temp table. 

Use the results as basis for action plan 

The procedure is useful if confronted with  a Recover from a Suspect database . Attempt to save the database for analysis

CREATE TABLE #tempResults
(Error nvarchar(100),
[Level] nvarchar(100),
[State] nvarchar(100),
[MsgTxt] nvarchar(100),
[RepairLvl] nvarchar(100),
[Status] nvarchar(100),
[DbId] nvarchar(100),
[ObjectID] nvarchar(100),
[IndexId] nvarchar(100),
[PartitionId] nvarchar(100),
[AllocUnitId] nvarchar(100),
[File] nvarchar(100),
[Page] nvarchar(100),
[Slot] nvarchar(100),
[RefFile] nvarchar(100),
[RefPage] nvarchar(100),
[Ref Slot] nvarchar(100),
[Alocation] nvarchar(100)
)

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 TABLERESULTS' 
INSERT INTO #tempResults
EXEC (@cmd) 
FETCH NEXT FROM crs INTO @username, @tablename 
END 
PRINT 'FINISHED' 
CLOSE crs 
      DEALLOCATE crs
SELECT * FROM #tempResults
DROP TABLE #tempResults
    

Related Posts

SQL Server – The server is down and when to call for External Help

DBCC CHECKDB

SQL Server - sys.dm_os_loaded_modules