Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – SQL Server All Table & Index Sizes in MB’

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)

Advertisements

Read Full Post »