A customer asked me today, 'how much space are all my tables taking?'. Great question! It is very helpful to know the size of your tables; not just the rowcount, but the physical space each table is using. This is a quick query that you can use for just that. I am JOINing sys.tables, sys.indexes and a couple more catalog views, and returning back the record count and the size of the data and index pages for each of your tables.
WITH spaceUsed
AS (
SELECT
o.object_id,
s.name [SchemaName],
o.name [TableName],
CASE MAX(i.index_id) WHEN 1 THEN 'Clustered' ELSE 'Heap' END [IndexType],
SUM(p.rows) [RecordCount],
SUM(a.total_pages)
[DataPages]
FROM
sys.tables o INNER JOIN sys.indexes i
ON i.object_id = o.object_id INNER JOIN sys.partitions p
ON p.object_id = o.object_id
AND p.index_id = i.index_id INNER JOIN sys.allocation_units a
ON a.container_id = p.partition_id INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE
o.type = 'U'
AND i.index_id IN(0,1)
GROUP BY
s.name,
o.name,
o.object_id
),
IndexPages
AS (
SELECT
o.object_id,
o.name [TableName],
SUM(a.total_pages)
[IndexPages]
FROM
sys.objects o INNER JOIN sys.indexes i
ON i.object_id = o.object_id INNER JOIN sys.partitions p
ON p.object_id = o.object_id
AND p.index_id = i.index_id INNER JOIN sys.allocation_units a
ON a.container_id = p.partition_id
WHERE
i.index_id <> 0
GROUP BY
o.name,
o.object_id
)
-- return details
SELECT
s.SchemaName,
s.TableName,
s.IndexType,
s.RecordCount,
s.DataPages,
(s.DataPages
* 8)
[DataPageSizeKB],
ISNULL(i.IndexPages, 0) IndexPages,
(ISNULL(i.IndexPages, 0) * 8)
[IndexPageSizeKB]
FROM
spaceUsed s LEFT
JOIN IndexPages i
ON s.object_id = i.object_id
ORDER BY
s.SchemaName,
s.TableName
This is the output from AdventureWorks2012 on my own instance:
Nothing too complex. There's probably several other details you could include as well. Take a look at this reference to the each of the catalog views I used, as well as many more:
