Sunday, May 23, 2021

Show Size of All Tables in Current Database in SQL Server

 You can find the size of all tables in the current database by using below sql script.

SQL Code:

USE AdventureWorks2017

SELECT + '.' + t.NAME AS TableName,
SUM(a.used_pages)*8 AS 'TableSizeKB' --a page in SQL Server is 8kb
FROM sys.tables t
JOIN sys.schemas s on t.schema_id = s.schema_id
LEFT JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
LEFT JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id
ORDER BY + '.' + t.NAME


Related Article:

No comments:

Post a Comment