SQL

I'm sure most of you use DBCC commands and have experience with the WITH TABLERESULTS option to put their output into a table. Did you know that most of the DBCC CHECK commands also accept WITH TABLERESULTS:

Error

Level

State

MessageText

RepairLevel

Status

DbId

ObjectId

IndexId

PartitionId

AllocUnitId

File

Page

Slot

RefFile

RefPage

RefSlot

Allocation

2593

10

1

There are 1444 rows in 11 pages for object "sys.sysrowsetcolumns".

NULL

0

4

4

1

0

0

0

0

0

0

0

0

1

2593

10

1

There are 209 rows in 2 pages for object "sys.sysrowsets".

NULL

0

4

5

1

0

0

0

0

0

0

0

0

1

2593

10

1

There are 243 rows in 3 pages for object "sysallocunits".

NULL

0

4

7

1

0

0

0

0

0

0

0

0

1

2593

10

1

There are 2 rows in 1 pages for object "sys.sysfiles1".

NULL

0

4

8

0

0

0

0

0

0

0

0

0

1

2593

10

1

There are 1444 rows in 12 pages for object "sys.syshobtcolumns".

NULL

0

4

13

1

0

0

0

0

0

0

0

0

1

2593

10

1

There are 209 rows in 2 pages for object "sys.syshobts".

NULL

0

4

15

1

0

0

0

0

0

0

0

0

1

2593

10

1

There are 0 rows in 0 pages for object "sys.sysftinds".

NULL

0

4

25

1

0

0

0

0

0

0

0

0

1

This isn't documented in Books Online but it works with CHECKALLOC, CHECKDB, CHECKFILEGROUP and CHECKTABLE. Since the output is the same for all CHECK commands you can establish a fixed table layout to hold the results:

And then do a SELECT on the DBCC_Results table. If there are no rows, congratulations, your databases have no errors!

Naturally you'll need to clean up the table between runs. If you wanted to keep the data for reporting purposes, you can add columns to DBCC_Results like RunDate, CheckType, etc., and specify a column list for the INSERT statement. I'll leave that as an exercise for the reader.