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
Enjoy :)
Saturday, June 23, 2007 2:54 AM
