I am using the SSIS Monitoring tool that was published on the SQL Mag site http://www.sqlmag.com/Articles/ArticleID/97840/97840.html. I guess using SSIS is a common way of building a SQL monitoring tool as I have seen a few examples of similar SSIS packages on the web.
I was able to append the server and database name to the output of showcontig. The only issue is showcontig is very slow. I still support a number of SQL Server 2000 instances so I am stuck with using it to get these stats, in 2005 / 2008 using the DMV's is a better option.
I used sp_MSforeachdb to get this working... not the best way of doing this.
EXEC sp_MSforeachdb @command1 = 'insert into [tempdb].[dbo].[contigHold]
exec (''USE [?] DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES'')', @command2 = 'insert into [tempdb].[dbo].[contigHoldforEachDB] select CONVERT(char(100), SERVERPROPERTY(''Servername'')) AS Server,''?'' as DatabaseName, * from [tempdb].[dbo].[contigHold]', @Command3 = 'truncate table [tempdb].[dbo].[contigHold]'
If there is a better way of getting this information from SQL 2000 I would like to know.
Blog: http://crazyemu.wordpress.com/
Twit: @crazySQL