Friday, 15 February 2013

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

No comments:

Post a Comment