List all indexes with keys, description and size

-- 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