This is a retooling of Thava's block of code that he shared with us in November to count the number of indexes in all tables and is designed to build on that output to better understand how it contributes to storage requirements. I arbitrarily chose to sort on the total table size but left a commented out line as an example to switch to sorting by total rows.
If anyone can think of any tweaks, changes, or questions please enjoy building onto it to better the community
Tony Trus
set nocount on
if exists(select name from tempdb..sysobjects where name='##tmp') drop table ##tmp
if exists(select name from tempdb..sysobjects where name='##tmp2') drop table ##tmp2
--first temp table can hold the grouped data
--credit to Thava for gathering index counts
SELECTt.name AS TableName, t.[object_id],
SUM ( CASE WHEN i.is_primary_key = 1 THEN 1 ELSE 0 END ) AS Primarykey,
SUM ( CASE WHEN i.[type] = 1 THEN 1 ELSE 0 END ) AS ClusteredIndex,
SUM ( CASE WHEN i.[type] = 2 THEN 1 ELSE 0 END ) AS NonClusteredIndex,
SUM ( CASE WHEN i.[type] = 0 THEN 1 ELSE 0 END ) AS HeapIndex,
COUNT ( * ) TotalNoofIndex into ##tmp2
FROM sys.tables t
LEFT OUTER JOIN sys.indexes i
ON i.[object_id] = t.[object_id]
GROUP BY
t.name, t.[object_id]
order by TableName asc
--second temp table will hold the sizes
create table ##tmp(nam varchar(50), rows int, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))
go
declare @tblname varchar(50)
declare tblname CURSOR for select name from sysobjects where xtype='U'
open tblname
Fetch next from tblname into @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
insert into ##tmp
exec sp_spaceused @tblname
FETCH NEXT FROM tblname INTO @tblname
END
CLOSE tblname
deallocate tblname
go
update ##tmp set
res = round((cast(REPLACE(res,' KB','') as real) / 1024),-1), --convert to MB
data = round((cast(REPLACE(data,' KB','') as real) / 1024),-1),
ind_sze = round((cast(REPLACE(ind_sze,' KB','') as real) / 1024),-1),
unsed = round((cast(REPLACE(unsed,' KB','') as real) / 1024),-1)
select nam Table_Name,rows Total_Rows,res Total_Size_MB,data Data_size_MB,ind_sze Index_Size_MB,unsed Unused_Space,##tmp2.TotalNoofIndex as [Number of Indexes],##tmp2.ClusteredIndex as [NumClustered], ##tmp2.NonClusteredIndex as [NumNonClustered], ##tmp2.HeapIndex as [NumHeap], ##tmp2.Primarykey as [Has Primary Key]
from ##tmp
join ##tmp2 on ##tmp.nam = ##tmp2.TableName
order by cast(res as real) desc
--order by rows desc
drop table ##tmp
drop table ##tmp2