My spaced used scripts

I suppose everyone has a set of scripts they lean on to help make sense of it all. These scripts are my version of sp_spaceused, one to query a specific table and the other at db level. Admittedly these are rough around the edges, that said they quickly allow me to break down a table and its index usage.

Here is the table specific one with some sample output and sp_spaceused output for comparison. I like the fact I can distinguish between standard data pages and for example, row overflow or lob pages. An interesting column is non_data_mb, which is the spaced used to store the index information (non leaf levels).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
declare @schema sysname = ''
      , @table  sysname = ''
 
select quotename(OBJECT_SCHEMA_NAME(p.object_id)) + '.' + quotename(OBJECT_NAME(p.object_id)) [table_name] 
     , p.index_id
     , i.name [index_name]
     , p.rows
     , au.type_desc
     , convert(decimal(10, 3), au.total_pages /128.) [reserved_mb]
     , convert(decimal(10, 3), au.data_pages /128.) [data_mb]
     , au.data_pages
     , convert(decimal(10, 3), (au.used_pages - au.data_pages) /128.) [non_data_mb]
     , convert(decimal(10, 3), (au.total_pages - au.used_pages) /128.) [unused_mb]
from   sys.partitions p
join   sys.allocation_units au on p.hobt_id = au.container_id
join   sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
where  p.object_id = object_id(quotename(@schema) + '.' + quotename(@table));

MySpaceUsedOutput

The code for both scripts is available here in Github.

Enjoy.