It's been a long few days of building slide decks and other content and I just had to stop for a bit and take care of my internals-hacking withdrawal symptoms!

While I was at Microsoft, I wrote some code in the Storage Engine to very easily return all the IAM chains/allocation units (see this post for more details of the internals of those), what type they are, and the relevant page IDs (first, root, first-IAM) so I could go spelunking with DBCC PAGE. Since I left six months ago, it's one of the things I've been missing using when poking around on customer sites, so this afternoon I sat down and wrote the equivalent in T-SQL, using the undocumented sys.system_internals_allocation_units DMV. The output is easy to match up to sys.partitions but the page IDs are formatted in byte-reversed hex so a little tweaking was needed to extract the fields and make them human readable - I've put them into the same format that all SQL Server error messages use when giving a page number.

So - I present to you sp_AllocationMetadata. I was having all kinds of trouble using it in other databases (trying to figure out a way to change database contexts in the SP) until I remembered that you can create an SP in master and mark it as a system object using the undocumented sys.sp_MS_marksystemobject SP. This makes any SP execute in the context of the database from where it is called - extremely useful when you're querying against a database's system catalog views.

[Edit: Kalen pointed out that the DMV *is* documented, but just not in the BOL index. Even better - that means the SP below isn't doing anything dodgy - thanks Kalen!]

The SP can be called with an optional object name parameter, in which case it will only give you back the allocation metadata for that object. If you don't specify a parameter, it gives you back the allocation metadata for all objects in the database. Here's an example of the output:

USE AdventureWorks;
GO

EXEC sp_AllocationMetadata 'HumanResources.Employee';
GO

Object Name   Index ID   Alloc Unit ID       Alloc Unit Type   First Page   Root Page   First IAM Page
------------- ---------- ------------------- ----------------- ------------ ----------- ----------------
Employee      1          72057594050379776   IN_ROW_DATA       (1:588)      (1:594)     (1:593)
Employee      2          72057594055491584   IN_ROW_DATA       (1:2141)     (1:2144)    (1:2142)
Employee      3          72057594055557120   IN_ROW_DATA       (1:2146)     (1:2149)    (1:2147)
Employee      4          72057594055622656   IN_ROW_DATA       (1:2150)     (1:2150)    (1:2151)
Employee      5          72057594055688192   IN_ROW_DATA       (1:2153)     (1:2153)    (1:2154)

You'll notice there are only IN_ROW_DATA allocation units - that's because this table doesn't have any LOB data or an variable-length columns that have been pushed off-row (producing LOB_DATA and ROW_OVERFLOW_DATA allocation units, respectively). So - it only shows what actually exists (rather than creating NULL values, for instance).

Below is the script that creates the SP, and I've included it as an attachment too.

Ah - that feel's better Happy spelunking!