Script to check integrity on all tables in a database 1

[outofservice]

-- NB: Works on version 2000 only!
-- Script to check integrity on all tables in a database
-- Uses DBCC CHECKTABLE
-- exec lb_check_table_all

-- If procedure exists, drop it
IF EXISTS (SELECT so.name
FROM sysobjects so
WHERE so.name = N'sp_check_table_all'
AND so.type = 'P')
DROP PROCEDURE sp_check_table_all
GO

-- Create procedure
CREATE PROCEDURE sp_check_table_all AS
SET NOCOUNT ON

-- Declare variable to contain the table name
declare @TableName varchar(50)

-- Print execution message to screen
print 'Executing sp_check_table_all ....'
print ' '

-- Declare cursor to get table name
declare TableCur CURSOR FOR

-- Get all user table names from 'sysobjects'
select so.name
from sysobjects so
where so.xtype = 'U'

-- Open cursor & fetch cursor item (table name) into @TableName
open TableCur
fetch TableCur into @TableName

-- If no items found by cursor, deallocate it
if @@fetch_status < 0
begin
deallocate TableCur
return
end

-- If cursor fetch status > 0
-- Return rows
while @@fetch_status >=0
begin
-- Print message to include table name
-- Run DBCC command with table name
print 'Displaying integrity info for ' + UPPER(@TableName)
DBCC CHECKTABLE (@Tablename)
print ' '

-- Fetch next item from cursor
FETCH NEXT FROM TableCur INTO @TableName
END

-- Close cursor & deallocate
CLOSE TableCur
DEALLOCATE TableCur
GO

Lauryn Bradley
SQL Server DBA

 

[Witchy]

Brilliant! Cheers. I am fairly new to SQL Server and this
script will be really useful to me. Thanks!

 

[moonshadow]

You could also consider using sp_msforeachtable which Microsoft have helpfully placed in the master database which will execute a piece of SQL (such as a DBCC) for each table. Look elsewhere in Tek-Tips for the format.