SQL Server - DBCC checktable

08 October,2011 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - DBCC checktable


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer