Handy DBCC Feature
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:
CREATE TABLE [DBCC_Results] (error int,
level int,
state int,
MessageText nvarchar(2000),
RepairLevel nvarchar(30),
status int,
dbid smallint,
objectid int,
indexid smallint,
partitionid bigint,
allocunitid bigint,
[file] int,
page int,
slot int,
reffile int,
refpage int,
refslot int,
allocation int)
And then do the following:
INSERT [DBCC_Results] EXEC('dbcc checkdb([myDB]) with tableresults')
Now if all you care about are errors, you can add NO_INFOMSGS to the WITH clause:
INSERT [DBCC_Results] EXEC('dbcc checkdb([myDB]) with tableresults, no_infomsgs')
That will only show results if DBCC finds actual problems. And if you'd like to automate checking all databases on your server:
EXEC sp_msforeachdb 'INSERT [DBCC_Results] EXEC(''dbcc checkdb([?]) with tableresults, no_infomsgs'')'
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.
UPDATE: as usual, Tara beat me to it:
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=32042