Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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
    
Author: Jack Vamvas (http://www.sqlserver-dba.com)

Comments

Posted by Anonymous on 10 October 2011

Pingback from  Dew Drop – October 10, 2011 | Alvin Ashcraft's Morning Dew

Posted by ishaq sahhar on 22 November 2011

Hello

can I get more information on this part....

"Use the results as basis for action plan"

Leave a Comment

Please register or log in to leave a comment.