Ritesh Shah (Extreme-Advice.Com)

Calculate Table & Schema size in SQL Server :

Calculate Table & Schema Size

Keeping watch on database size, drive size, table size, index size etc. are few of the primary duty of DBA. I have already written few articles to cover this type of subject, for example:

Keep watch on your disk size

Keep watch on your database size

Calculate size of index

Today I am going to introduce two more TSQL Script which covers the size of each Table and size of each Schema in your SQL Server database. I used to have log of table size for each date so that I can compare the growth of table for future disk & other resource planning. It is really very mandatory for DBA to know the actual size of each table along with total number or rows available in that table, in order to make operation smooth and successful.

I have two TSQL Scripts which I use for getting an alert. You can set either of these scripts in SQL Server Job to get alert, or log data coming from these scripts, every day, in one table and use that table for future reference while taking any hardware related decision.

--SCRIPT NO 1

--Get total size of table and row count for each table

SELECT

SCHEMA_NAME(sysTab.SCHEMA_ID) as SchemaName,

sysTab.NAME AS TableName,

parti.rows AS RowCounts,

SUM(alloUni.total_pages) * 8 AS TotalSpaceKB,

SUM(alloUni.used_pages) * 8 AS UsedSpaceKB,

(SUM(alloUni.total_pages) - SUM(alloUni.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables sysTab

INNER JOIN

sys.indexes ind ON sysTab.OBJECT_ID = ind.OBJECT_ID and ind.Index_ID<=1

INNER JOIN

sys.partitions parti ON ind.OBJECT_ID = parti.OBJECT_ID AND ind.index_id = parti.index_id

INNER JOIN

sys.allocation_units alloUni ON parti.partition_id = alloUni.container_id

WHERE

sysTab.is_ms_shipped = 0

AND ind.OBJECT_ID > 255

AND parti.rows>0

GROUP BY

sysTab.Name, parti.Rows,sysTab.SCHEMA_ID

Order BY parti.rows desc

--SCRIPT NO 2

--Get total size of each schema available in your SQL Server database

SELECT

SCHEMA_NAME(sysTab.SCHEMA_ID) as SchemaName,

SUM(alloUni.total_pages) * 8 AS TotalSpaceKB,

SUM(alloUni.used_pages) * 8 AS UsedSpaceKB,

(SUM(alloUni.total_pages) - SUM(alloUni.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables sysTab

INNER JOIN

sys.indexes ind ON sysTab.OBJECT_ID = ind.OBJECT_ID  and ind.Index_ID<=1

INNER JOIN

sys.partitions parti ON ind.OBJECT_ID = parti.OBJECT_ID AND ind.index_id = parti.index_id

INNER JOIN

sys.allocation_units alloUni ON parti.partition_id = alloUni.container_id

WHERE

sysTab.is_ms_shipped = 0

AND ind.OBJECT_ID > 255

AND parti.rows>0

GROUP BY

sysTab.SCHEMA_ID

ORDER BY

TotalSpaceKB DESC

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

“Quick Learn“

Note: Microsoft Books online is a default reference of all articles but examples and explanations