Integrity check on selected tables

We do a general DB integrity check weekly through a DB maintenance plan on our SQL 2000 S.E. servers. I'd like to do a nightly integrity check on just a few tables on very large databases. The DB Maintenance Plan Wizard does not appear to allow this.

What T-SQL can be used to accomplish this?
Does Enterprise Manager offer a way to do this?See DBCC CHECKTABLE in BOL|||[See DBCC CHECKTABLE in BOL [/SIZE][/QUOTE]

I have checked into this in the past. However, it doesn't appear to allow me to list a group of tables to check. I get a parameter incorrect for this statement.

I'd like to do something where I list several tables. Or, all tables between the letters A and D. In this manner, I may be able to run 7 days of maintenace weekly but do integrity checking piecemeal.|||Originally posted by Fulvio Hayes
We do a general DB integrity check weekly through a DB maintenance plan on our SQL 2000 S.E. servers. I'd like to do a nightly integrity check on just a few tables on very large databases. The DB Maintenance Plan Wizard does not appear to allow this.

What T-SQL can be used to accomplish this?
Does Enterprise Manager offer a way to do this?

You should be able to to create a maint plan that just does integrity checks. On my SQL7 I can go through and click off the backup portions and just turn on the nightly integrity checks.|||Originally posted by Fulvio Hayes
[See DBCC CHECKTABLE in BOL

I have checked into this in the past. However, it doesn't appear to allow me to list a group of tables to check. I get a parameter incorrect for this statement.

I'd like to do something where I list several tables. Or, all tables between the letters A and D. In this manner, I may be able to run 7 days of maintenace weekly but do integrity checking piecemeal. [/SIZE][/QUOTE]

Create sp:

dbcc checktable for table from list (get list from table will be better)

dbcc checktable 'tableA'
dbcc checktable 'tableB'
.................
Also, you can save results of checking in table or return as recordset.

insert #tmp
dbcc checktable 'tableA'
insert #tmp
dbcc checktable 'tableB'

select * from #tmp|||Load a list of tables into a cursor dataset, and then loop through the set to execute your DBCC.

If you store the name of the last table completed, you can start with the next table the following night. You could even define a processing period by setting your code to exit the loop after a certain number of minutes, or at a specified hour.

blindman|||Originally posted by blindman [/i]
Load a list of tables into a cursor dataset, and then loop through the set to execute your DBCC.

If you store the name of the last table completed, you can start with the next table the following night. You could even define a processing period by setting your code to exit the loop after a certain number of minutes, or at a specified hour.

blindman

Thanks! I'll try that. In some cases, I may use 'snails' recommendation to use checktable repeatedly for a small number of recurring tables. But for my larger, high I/O databases I'll look to going the route of the cursor dataset you recommend. I'll let youknow how it works.

Fulvio