Calculate Table & Schema size in SQL Server
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 database size
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
Note: Microsoft Books online is a default reference of all articles but examples and explanations