In this guide, we will learn How to Get the size of all Tables in a Database using SQL Server. There are multiple ways, let’s take a look at them one by one.
#Solution-1: SQL Query
The query joins information from the system catalog views such as “sys.tables”, “sys.indexes”, ”sys.partitions”, and “sys.allocation_units” to calculate the size of each table in KB(kilobytes) and as well as MB(megabytes).
It provides a detailed overview of all tables in the specified database, including the table name row count, total size, used size, and unused side. The results are ordered by total size in descending order, so you can easily identify the largest tables.
SELECT
t.name AS TableName,
s.name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceInKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceInMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceInMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceInKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceInMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.object_id = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.name NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.object_id > 255
GROUP BY
t.name, s.name, p.rows
ORDER BY
TotalSpaceInMB DESC, t.name;
Results:
#Solution-2:CTE Query
with CTE as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END) as pages
FROM sys.dm_db_partition_stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
,cte2 as(select
cte.TableName,
(cte.pages * 8.) as TableSizeInKB,
((CASE WHEN cte.used_pages_count > cte.pages
THEN cte.used_pages_count - cte.pages
ELSE 0
END) * 8.) as IndexSizeInKB
from CTE
)
select TableName,TableSizeInKB,IndexSizeInKB,
case when (TableSizeInKB+IndexSizeInKB)>1024*1024
then cast((TableSizeInKB+IndexSizeInKB)/1024*1024 as varchar)+'GB'
when (TableSizeInKB+IndexSizeInKB)>1024
then cast((TableSizeInKB+IndexSizeInKB)/1024 as varchar)+'MB'
else cast((TableSizeInKB+IndexSizeInKB) as varchar)+'KB' end [TableSizeIn+IndexSizeIn]
from CTE2
order by 2 desc
Result:
#Solution-3:T-SQL |Stored Procedure
-- Using T-SQL
DECLARE @table_name sysname
DECLARE table_list_cursor
CURSOR FOR SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
IF EXISTS ( SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE table_name = '##TABLE_RES')
BEGIN
DROP TABLE ##TABLE_RES END CREATE TABLE ##TABLE_RES( Name nvarchar(255),
Rows int, reserved varchar(18), Data varchar(18), index_size varchar(18),
Unused varchar(18)) OPEN table_list_cursor
FETCH NEXT FROM table_list_cursor INTO @table_name
INSERT INTO ##TABLE_RES exec sp_spaceused @table_name
WHILE @@FETCH_STATUS = 0
BEGIN FETCH NEXT FROM table_list_cursor INTO @table_name
INSERT INTO ##TABLE_RES exec sp_spaceused @table_name
END CLOSE table_list_cursor
DEALLOCATE table_list_cursor
SELECT * from ##TABLE_RES order by rows desc
#Solution-4: To Get the size of all tables in the SQL Server Database
In SQL Server, it is an easier way to get the size of all tables in the database is to use the Standard Report feature available in SQL Server Management Studio.
- Open the SSMS (SQL Server Management Studio)
- Now, Right Click on the Selected Database.
- Select Reports
- Next, Select Standard Reports
- Then Click on Disk Usage by Table
Output:
See Also:
