Collecting multi-resultset output from DBCC SHOW_STATISTICS using DTS
Someone asked how to collect the multi-resultset output from DBCC SHOW_STATISTICS in the SQL MVP forum today. This is a tricky thing to do in TSQL because there are no features on TSQL to catch multi-resultset output from stored procs, DBCC etc.
You can use the INSERT / EXEC sp.. technique to collect output from procs that return ONE resultset, but that doesn't help if the proc returns MANY resultsets & you need info from the [n]th resultset.
You can use the WITH TABLERESULTS option with some DBCC commands, but again, this is no help if the DBCC statement returns multiple resultsets (such as DBCC SHOW_STATISTICS) & you need to collect all of the output.
I'd previously written component code to do something like this in VB & C# in the past, but it struck me when I saw this question today that another option would be to use DTS's ActiveX script object.
SO I flung together a few scripts & offered them up to the MVP forum as an option. I'm sure those guys will improve on these scripts, but I've loaded them up to the Resources section in case they're of any use to anyone.
These have been put together with DBCC SHOW_STATISTICS hard-coded, but it would only take a little more work to make the solution a little more generic. I'm just a little lazy right now. (c:
The scripts can be found here:
http://www.sqlserver.org.au/Resources/ViewResource.aspx?resourceId=34