Overview data from all tables

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.  





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
SELECT t.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 x'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