http://www.sqlservercentral.com/blogs/basits-sql-server-tips/2012/07/01/determine-space-used-for-each-table-in-a-sql-server-database/

Printed 2014/11/23 02:27PM

Determine space used for each table in a SQL Server database

2012/07/01

I have written following Microsoft SQL Server T-SQL scirpt to quickly determine space used for each table in a SQL Server database. This script returns following information for each table in the database:

Script:

USE [<Database Name>]
GO
WITH DataPages AS
(
SELECT o.object_id
, COALESCE(f.name,d.name) AS Storage
, s.name AS SchemaName
, o.name AS TableName
, COUNT(DISTINCT p.partition_id) AS NumberOfPartitions
, CASE MAX(i.index_id) WHEN 1 THEN 'Cluster' ELSE 'Heap' END AS TableType
, SUM(p.rows) AS [RowCount]
, SUM(a.total_pages) AS DataPages
FROM sys.tables o
JOIN sys.indexes i 
    ON i.object_id = o.object_id
JOIN sys.partitions p 
    ON p.object_id = o.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units a 
    ON a.container_id = p.partition_id
JOIN sys.schemas s 
    ON s.schema_id = o.schema_id
LEFT JOIN sys.filegroups f 
    ON f.data_space_id = i.data_space_id
LEFT JOIN sys.destination_data_spaces dds
    ON dds.partition_scheme_id = i.data_space_id
    AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups d ON d.data_space_id = dds.data_space_id
WHERE o.type = 'U'
AND i.index_id IN (0,1)
GROUP BY s.name
, COALESCE(f.name,d.name)
, o.name
, o.object_id )
,IndexPages AS
(SELECT o.object_id
, o.name AS TableName
, COALESCE(f.name,d.name) AS Storage
, COUNT(DISTINCT i.index_id) AS NumberOfIndexes
, SUM(a.total_pages) AS IndexPages
FROM sys.objects o
JOIN sys.indexes i 
    ON i.object_id = o.object_id
JOIN sys.partitions p 
    ON p.object_id = o.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units a 
    ON a.container_id = p.partition_id
LEFT JOIN sys.filegroups f 
    ON f.data_space_id = i.data_space_id
LEFT JOIN sys.destination_data_spaces dds
    ON dds.partition_scheme_id = i.data_space_id
    AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups d 
    ON d.data_space_id = dds.data_space_id
WHERE i.index_id <> 0
GROUP BY o.name
, o.object_id
, COALESCE(f.name,d.name))
 
SELECT t.[SchemaName]
, t.[TableName]
, t.[TableType]
, t.[Storage] AS FileGroupName
, t.[NumberOfPartitions]
, t.[RowCount]
, t.[DataPages]
, (t.[DataPages] * 8) AS SizeOfDataPagesKB
, ISNULL(i.[NumberOfIndexes],0) AS NumberOfIndexes
, ISNULL(i.[IndexPages],0) AS IndexPages
, (ISNULL(i.[IndexPages],0) * 8) AS SizeOfIndexPagesKB
FROM DataPages t
LEFT JOIN IndexPages i
    ON i.object_id = t.object_id
    AND i.Storage = t.Storage;
GO

 
Sample output after running this against the AdventureWorks database:

I hope you find it useful! :)



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.