[Tricks and Tips] Using DBCC IND and DBCC PAGE to view a table without affecting DM_DB_INDEX_USAGE_STATS

First off: don't use this in production, or at least understand what you're/I'm doing.  This uses two undocumented DBCC commands.  There IS a replacement for DBCC IND in 2012+, but I needed this for older servers (and besdies, DBCC PAGE would still be unsupported).

Problem: I'm consolidating servers. As part of that, I need to see what tables the database has that are used. I use a script elsewhere on the blog for that, one that uses a DMV called SYS.DM_DB_INDEX_USAGE_STATS. It, unsurprisingly, tells you the last time an index was used - but can also be used to tell when a query last touched a table.  So if it's had inserts for 2 years but no selects, odds are you need to reconsider whether you need it.  (An aside - you might ought to consider rebooting your machines more often than once every 2 years)

However, I don't want to do a SELECT from the table, since that would affect the DMV.  Technically I have a way around that - I use my EPR (again, another post) to save out the stats twice a day.  However, when you go back and look at 3 months of history, there's always the thought "was that me, or some process I don't know about that only runs once a quarter?".  Hence, this.

What we do is use two massively unsupported DBCC commands to get the list of pages allocated to a table, then use DBCC PAGE to show the contents of them.  Once that's done, we hopefully pull the data through a pivot into a viewable table.  By default it assumes there's a clustered index, but that's pretty optional - I did it that way due to an implied speed boost.  (After all, it has to pull back ALL the pages associated with a table.)

Nifty bit: you don't need to use TRACEON (3604) with PAGE if you use WITH TABLERESULTS. Didn't know that!

My "project nickname" for this is Medusa's Mirror, since you're not looking at the table directly.  Technically it should be Perseus' Shield, but I didn't think of it when I created the Gist.  Besides - it's alliterative.

Did some lightweight testing on some tables, though I don't have offrow data.  Use at your own risk.