Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Sometimes we need to list table sizes in SQL Server. For example, we need to find large tables before we start making Compression or before deciding which tables to partition. You can find table sizes using the following script.
You may want to read the following articles about Compression and Partition.
“How To Create Partition On SQL Server“,
“Sliding Window-Switch Partition-Split Range-Merge Range“,
“How To Find Partitioned Tables In The Database On SQL Server”
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SELECT tbl.NAME AS Table_Name, s.Name AS Schema_Name, p.rows AS Row_Count, SUM(au.total_pages) * 8 AS TotalSizeKB, SUM(au.used_pages) * 8 AS UsedSizeKB, (SUM(au.total_pages) - SUM(au.used_pages)) * 8 AS Unused_SpaceKB FROM sys.tables tbl INNER JOIN sys.indexes ind ON tbl.OBJECT_ID = ind.OBJECT_ID INNER JOIN sys.partitions p ON ind.OBJECT_ID = p.OBJECT_ID AND ind.index_id = p.index_id INNER JOIN sys.allocation_units au ON p.PARTITION_ID = au.container_id LEFT OUTER JOIN sys.schemas s ON tbl.SCHEMA_ID = s.SCHEMA_ID WHERE tbl.is_ms_shipped = 0 AND ind.OBJECT_ID > 255 GROUP BY tbl.Name, s.Name, p.Rows ORDER BY SUM(au.total_pages) DESC |
Descriptions of the columns specified in Script;
In the Total_SizeKB column, it shows the total size of the data in the table (excluding index),
In the Used_SizeKB colum, it shows how much of the total size is used.
In the Unused_SpaceKB column, it shows how much of the total size has not yet been used.
If you want to list the index sizes in the same query, you can use the following script.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | create table #Table_List ( Table_Name nvarchar(250), Row_Count varchar(50), Reserved varchar(50), Data varchar(50), Index_Size varchar(50), Unused_Space varchar(50) ) declare @Table_Name nvarchar(250) declare crs cursor for SELECT SCHEMA_NAME(schema_id) +'.'+ name As Table_Name from sys.tables ORDER BY name open crs fetch crs into @Table_Name while @@fetch_status = 0 begin insert into #Table_List exec sp_spaceused @Table_Name fetch crs into @Table_Name end close crs deallocate crs select * from #Table_List order by convert(int, substring(Reserved, 1, len(Reserved)-3)) desc drop table #Table_List |
