BPSoftware.com Blog

Tuesday, November 25, 2008

SQL: DBCC CHECKTABLE on multiple tables

Part of decent SQL database maintenence is periodically checking the integrity, both logical and physical, of database objects. SQL Server has the DBCC CHECKDB command that will check the integrity of the objects and report back the results. DBCC CHECKDB runs CHECKALLOC, CHECKTABLE and CHECKCATALOG on the specified database.

in SQL 2005 you can use table variables if you do not need or want to store the data you can store it in a table variable instead of a temporary table

in SQL 2005 you can use table variables if you do not need or want to store the data you can store it in a table variable instead of a temporary table

DBCC CHECKTABLE allows you to check the integrity of a specified table or indexed view. If there are a large number of tables to check, the task of running the command for each tablename could be time consuming. Creating a script that loops through a set of database tables and runs DBCC CHECKTABLE for each, is a bit easier.

The sys.objects contains each of the user-defined schema objects in a database. Rows that are of [Type] = 'U' are tables. This makes it easy to get all the tables within a database (of course other filters can be applied). The following script will loop through and run the DBCC CHECKTABLE command for each table in the database (the command can be easily replaced to perform another action on each returned table):

-- set options
3

SET NOCOUNT ON;
4

SET ANSI_PADDING ON;
5

SET ANSI_NULLS ON;
6

SET QUOTED_IDENTIFIER ON;
7

-- declare table to store table names
10

-- in SQL 2005 you can use table variables
11

-- if you do not need or want to store the data you can store it in a table
12

-- variable instead of a temporary table
13

DECLARE @tablenames TABLE (
14

TableName VARCHAR(255)
15

);
16

-- populate table with tablenames
20

INSERT INTO @tablenames
21

SELECT
22

name
23

FROM
24

sys.objects
25

-- additional filters can be set to narrow table selection
26

WHERE
27

([type] = 'U');
28

-- Declare a cursor to loop through table
32

DECLARE tablenames INSENSITIVE SCROLL CURSOR
33

FOR
34

SELECT
35

TableName
36

FROM
37

@tablenames;
38

-- Open the cursor.
43

OPEN tablenames
44

-- declare variables
48

DECLARE @curtablename AS VARCHAR(255);
49

DECLARE @cmd AS VARCHAR(1024);
50

-- Loop through all the tables
54

WHILE (1=1)
55

BEGIN
56

FETCH NEXT FROM tablenames INTO @curtablename;
57

IF @@fetch_status <> 0 BREAK;
58

-- run the command
59

set @cmd = 'DBCC CHECKTABLE ([' + rtrim(@curtablename) + '])';
60

exec (@cmd)
61

print @cmd + CHAR(10) + CHAR(13);
62

end
63

-- Close cursor
67

CLOSE tablenames;
68

DEALLOCATE tablenames;
69

Labels: Code, SQL

posted by Brad Prendergast at 9:04:00 PM

Comments: