August 27, 2011 at 6:49 am
Hi,
I got script Mr.RBarryYoung from sqlserver central one of the threads, this is working with SQL 2005, 2008.
declare @PageSize float
select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'
SELECT SCHEMA_NAME(tbl.schema_id) as [Schema], tbl.*, idx.index_id,
CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusteredIndex],
ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
, ISNULL((select @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)
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
where i.object_id = tbl.object_id )
, 0.0) AS [IndexSpaceUsed]
, ISNULL((select @PageSize
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
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
where i.object_id = tbl.object_id)
, 0.0) AS [DataSpaceUsed]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
I want Index size belongs to HEAP, clustered, non clustered index and so on. could anyone have this type of script(table row count data size, index size) please give me.
Thanks
August 27, 2011 at 7:30 am
Remove the filter and idx.index_id < 2 if you want nonclustered indexes as well.
index_id 0 = heap
index_id 1 = clustered index
index_id >=2 = nonclustered index
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply