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