Anatoly Lubarsky Logo
programming, design, integration, games, music

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 :)


Related Posts:

Saturday, June 23, 2007 2:54 AM

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) March, 2011
(02) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(05) December, 2008
(04) November, 2008
(06) October, 2008
(04) September, 2008
(07) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(05) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(05) October, 2007
(07) September, 2007
(13) August, 2007
(11) July, 2007
(14) June, 2007
(16) May, 2007
(14) April, 2007
(10) March, 2007
(14) February, 2007
(20) January, 2007
(15) December, 2006
(12) November, 2006
(11) October, 2006
(13) September, 2006
(13) August, 2006
(12) July, 2006
(11) June, 2006
(12) May, 2006
(22) April, 2006
(30) March, 2006
(13) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(17) October, 2005
(17) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(15) May, 2005
(12) April, 2005
(21) March, 2005
(11) February, 2005
(12) January, 2005
(19) December, 2004
(13) November, 2004
(12) October, 2004
(15) September, 2004
(09) August, 2004
(25) July, 2004
(23) June, 2004
(31) May, 2004
(21) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs