How to Insert DBCC Output into MSSQL Table

How to Insert DBCC Output into MSSQL Table

Sometimes it is useful to save DBCC maintenance routines into MSSQL server
table, so the info will be logged. Some DBCC commands support this directly via
the ...

INSERT INTO #table EXEC('DBCC ...')

... type format (like for example DBCC USEROPTIONS), others don't
(like DBCC SHOWCONTIG). Fortunately there is some trick that will help
:)

DBCC can be used with WITH TABLERESULTS hint that outputs the output
in the table rowset format. So - here is a small code sample that demonstrates
the technique:

CREATE TABLE #x
(
    f1 VARCHAR(50)
   ,f2 VARCHAR(50)
   ,indexname VARCHAR(50)
   ,indexid VARCHAR(50)
   ,f5 VARCHAR(50)
   ,f6 VARCHAR(50)
   ,f7 VARCHAR(50)
   ,f8 VARCHAR(50)
   ,f9 VARCHAR(50)
   ,f10 VARCHAR(50)
   ,f11 VARCHAR(50)
   ,f12 VARCHAR(50)
   ,f13 VARCHAR(50)
   ,f14 VARCHAR(50)
   ,f15 VARCHAR(50)
   ,f16 VARCHAR(50)
   ,bestcount VARCHAR(50)
   ,actualcount VARCHAR(50)
   ,logicalfragmentation VARCHAR(50)
   ,f20 varchar(50))

INSERT #x
  EXEC('DBCC SHOWCONTIG(MyTable) WITH ALL_INDEXES, TABLERESULTS')

SELECT *
  FROM #x

DROP TABLE #x