Useful SQL Query Snippets: Table Sizes

I can’t count the number of times I’ve used this query at various sites. It started when I needed to use

As with many things these days, I found it on StackOverflow but I want to document it here in case it ever goes missing.

While it makes use of the sys. tables (which numerous articles say may change in the future ) as opposed to INFORMATION_SCHEMA, the end result compares the size of tables while using the partitions and tables for calculating sizes.

We use this often for identifying which tables need to be cleared out or reduced when creating QA or test environments that are limited in environments due to size. Our production system is well over 200GB and if I need to do quick development, copying a DB that size is just dumb. This query lets me see where I need to “trim the fat”.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

The other function I use a lot for a similar purpose is figuring out the size of image fields. That’s part of the reason why the above query uses the partitions and allocation units. For image and blob fields, I use DATALENGTH.

select DATALENGTH(blob_field) from table name

Related