Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Add Comment
Please, Sign In to add comment