T-SQL Query to find Index size of all tables | SansSQL

Tuesday, February 12, 2013

T-SQL Query to find Index size of all tables

This query gives results in 2 parts by making use of a undocumented stored procedure sp_MSIndexSpace
  1. The size of each individual index of a table
  2. The total size of index on a table
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name='TempIndexSpace')
DROP TABLE tempdb..TempIndexSpace
CREATE TABLE tempdb..TempIndexSpace 
(ObjectName nvarchar(100)
 ,IndexID int
 ,IndexName nvarchar(100)
 ,[IndexSize(KB)] int
 ,Comments nvarchar(max))
exec sp_msforeachtable 'INSERT INTO tempdb..TempIndexSpace (IndexID,IndexName,[IndexSize(KB)],Comments) EXEC sp_MSIndexSpace [?];
UPDATE tempdb..TempIndexSpace SET ObjectName=''?'' WHERE ObjectName IS NULL'

-- This gives output per index
SELECT * FROM tempdb..TempIndexSpace

-- This gives output per table
SELECT  ObjectName,SUM([IndexSize(KB)]) AS [Index Size (KB)] FROM tempdb..TempIndexSpace
GROUP BY ObjectName

No comments:

Post a Comment