TableSizes.sql

SET NOCOUNT ON

IF OBJECT_ID('tempdb..##INDEXTEMP') IS NOT NULL begin exec sp_executesql N'DROP TABLE ##INDEXTEMP' end

EXEC sp_MSforeachtable
@command1 = N'INSERT INTO ##INDEXTEMP ([name], [rows], [reserved], [data], [index_size], [unused]) exec sp_spaceused ''?'', ''true'';'
, @precommand = N'CREATE TABLE ##INDEXTEMP ([id] int identity(1,1) not null,[name] sysname,[rows] int,[reserved] varchar(20),[data] varchar(20),[index_size] varchar(20),[unused] varchar(20))'
, @postcommand = N'
SELECT
[name], [rows], [reserved], [data], [index_size], [unused]
, convert(varchar, coalesce(
    cast( replace([index_size], '' KB'', '''') as bigint ) * 100 /
    nullif(cast( replace([data], '' KB'', '''') as bigint ), 0)
    , 0)) + ''%'' index_data_ratio
, convert(varchar, coalesce(
    cast( replace([unused], '' KB'', '''') as bigint ) * 100 /
    nullif(cast( replace([reserved], '' KB'', '''') as bigint ), 0)
    , 0)) + ''%'' unused_used_ratio
FROM ##INDEXTEMP
ORDER BY 1;
SELECT sum([rows]) [Total Rows], ''KB'' [Factor]
 , sum(cast(replace([reserved], '' KB'', '''') as decimal(20, 3))) [Reserved]
 , sum(cast(replace([data], '' KB'', '''') as decimal(20, 3))) [Data]
 , sum(cast(replace([index_size], '' KB'', '''') as decimal(20, 3))) [Index]
 , sum(cast(replace([unused], '' KB'', '''') as decimal(20, 3))) [Unused]
FROM ##INDEXTEMP
UNION ALL SELECT null, ''MB''
 , sum(cast(replace([reserved], '' KB'', '''') as decimal(20, 3))) / 1024.0
 , sum(cast(replace([data], '' KB'', '''') as decimal(20, 3))) / 1024.0
 , sum(cast(replace([index_size], '' KB'', '''') as decimal(20, 3))) / 1024.0
 , sum(cast(replace([unused], '' KB'', '''') as decimal(20, 3))) / 1024.0
FROM ##INDEXTEMP
UNION ALL SELECT null, ''GB''
 , sum(cast(replace([reserved], '' KB'', '''') as decimal(20, 3))) / 1024.0 / 1024.0
 , sum(cast(replace([data], '' KB'', '''') as decimal(20, 3))) / 1024.0 / 1024.0
 , sum(cast(replace([index_size], '' KB'', '''') as decimal(20, 3))) / 1024.0 / 1024.0
 , sum(cast(replace([unused], '' KB'', '''') as decimal(20, 3))) / 1024.0 / 1024.0
FROM ##INDEXTEMP;
DROP TABLE ##INDEXTEMP'

SET NOCOUNT OFF