Today, I received a query enquiring how to calculate the table Size, its index size along with the total number of rows, as the client’s database file size suddenly grew from MBs to GBs and they wanted to know what was inserted in which tables that caused this instant growth.
So, I started searching for solution over the web, but most of the solutions use cursor to calculate each table & its index size along with the total number of rows. I could not get what I was looking for, so, I thought of writing this and calculate it with an optimized query and convert the table & its index size to MBs instead of KBs because windows keep the file sizes in MBs also.
Given below is the Script :
Declare @PageSize as int Select @PageSize= low/1024.0 from master.dbo.spt_values Where Number=1 And type='E' select object_name(i.object_id) as [Table Name] , Convert(numeric(18,3),Convert(numeric,@PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) / 1024) As [Data Space Used (In Mbs)] ,Convert(numeric(18,3), Convert(numeric(18,3),@PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) / 1024) As [Index Space Used (In Mbs)] , SUM(Case When p.index_id=1 and a.type=1 Then p.rows else 0 end) As [Total No of Rows] FROM sys.indexes as i JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id JOIN sys.allocation_units as a ON a.container_id = p.partition_id LEFT Join sys.tables t ON i.object_id=t.object_id Where t.type='U' Group By object_name(i.object_id) Order By object_name(i.object_id)
SQL Server Portal 