How To Find Table and Index Sizes in SQL Server

dbtut

6 years ago

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.

“SQL Server Compression“,

“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”

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

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.

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

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