List all indexes with keys, description and size

,

This script combines functionality of sp_helpindex and sp_spaceused to list all tables with individual indexes, their keys and description  (sp_spaceused cannot do that) and size in MB per individual index (sp_helpindex cannot do that either). Feel free to use and modify as you see fit. Hope it can help.

-- Select all table names, their individual indexes with keys, description
-- and disk size in MB into a temporary table. 

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @TabName varchar(100)

CREATE TABLE #T (TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200), IndexKeys varchar(200), IndexSize int)

DECLARE TCursor CURSOR FAST_FORWARD LOCAL FOR
	SELECT name FROM sysobjects WHERE xtype = 'U'
OPEN TCursor

FETCH NEXT FROM TCursor INTO @TabName
WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO #T (IndexName, IndexDescr, IndexKeys)
		EXEC sp_helpindex @TabName
		
		UPDATE #T SET TabName = @TabName WHERE TabName IS NULL
		
		FETCH NEXT FROM TCursor INTO @TabName
	END

CLOSE TCursor
DEALLOCATE TCursor
GO

DECLARE @ValueCoef int
SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N'E'

UPDATE #T SET IndexSize = 
	((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
		FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
			INNER JOIN #T T ON T.TabName = sysobjects.name AND T.IndexName = sysindexes.name

SELECT * FROM #T
--WHERE IndexDescr LIKE '%nonclustered%'  --Here various filters can be applied
ORDER BY TabName, IndexName 
GO

DROP TABLE #T
GO

Rate

5 (9)

Share

Share

Rate

5 (9)