Determine space used for each table in a SQL Server database

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:

  • SchemaName – Name of the schema.
  • TableName – Name of the table.
  • TableType – Type of the table e.g. Heap or Cluster.
  • FileGroupName – FileGroup where the table is stored.
  • NumberOfPartitions – Number of partitions in the table.
  • NumberOfRows – Number of rows in the table.
  • TotalDataPages – Number of data pages in the table.
  • SizeOfDataPagesKB – Size of data pages in KB.
  • NumberOfIndexes  – Number of indexes in the table.
  • NumberOfIndexPages  –  Number of index pages for the table indexes.
  • SizeOfIndexPagesKB – Size of index pages in KB.

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
	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
	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
	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
	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! :)

About these ads

One thought on “Determine space used for each table in a SQL Server database

  1. Very informative!

    How to:
    ORDER by t.SchemaName asc, t.TableName asc, t.[RowCount] desc
    ?

    Additional count(distinct column) for indexes keys density would be also appreciated.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s