SQL Server 2008 : Query to get list of Indexes from a All the Databases of Server

Below is the excellent query to fetch the list of the indexes used  across all the databases on the SQL Server. This is very helpful for the DBAs/ DB Professionals who maintains multiple databases distributed across SQL Server environments.

This script will provide consolidated list of the indexes with following information Database Name, Table Name, Index Name, comma separated list of columns used in Index,  comma separated list of columns INCLUDED in Index and type of Index (Clustered or non Clustered).

USE master
GO

DECLARE
@DBName VARCHAR(100),
@DBCount INT,
@IndexCount INT,
@SQL VARCHAR(8000) = '',
@Columns varchar(2000) = '',
@IncludedColumns varchar(2000) = '',
@IndexName VARCHAR(1000) = ''

DECLARE @tblDBList TABLE (DBName VARCHAR(100))
DECLARE @tblIndexList TABLE (IndexName VARCHAR(1000))

CREATE TABLE #tblIndexListSingleDB (DataBaseName VARCHAR(100), IndexName VARCHAR(1000), ColumnName VARCHAR(100), TableName VARCHAR(100), IndexType VARCHAR(100) , IsIncluded BIT)
CREATE TABLE #tblIndexListAllDB (RecordID INT Identity(1,1), DataBaseName VARCHAR(100), TableName VARCHAR(100), IndexName VARCHAR(1000), ColumnName VARCHAR(1000), IncludedColumns VARCHAR(2000), IndexType VARCHAR(100))

INSERT INTO @tblDBList SELECT name FROM SYS.DATABASES
                                  --optional condition if you wish to use to this query on limited database.
                                   --WHERE Name Like 'Like Condition%'
                                   --WHERE database_id BETWEEN 5 AND 20

SELECT @DBCount = COUNT(*) FROM @tblDBList

WHILE @DBCount > 0
BEGIN

SELECT TOP 1 @DBName = DBName FROM @tblDBList

SET @SQL = @SQL + ' INSERT INTO #tblIndexListSingleDB (DataBaseName, IndexName, ColumnName, TableName, IndexType, IsIncluded) ' + CHAr(13)
SET @SQL = @SQL + ' SELECT ' + '''' + @DBName + '''' + ' AS DataBaseName, IND.NAME AS IndexName, COL.NAME AS ColumnName, T.Name AS TableName, IND.TYPE_DESC AS IndexType , IC.IS_INCLUDED_COLUMN AS IsIncluded ' + CHAR(13)
SET @SQL = @SQL + ' FROM ' + @DBName + '.SYS.INDEXES IND ' + CHAR(13)
SET @SQL = @SQL + ' INNER JOIN ' + @DBName + '.SYS.INDEX_COLUMNS IC ON IND.OBJECT_ID = IC.OBJECT_ID AND IND.INDEX_ID = IC.INDEX_ID ' + CHAR(13)
SET @SQL = @SQL + ' INNER JOIN ' + @DBName + '.SYS.COLUMNS COL ON IC.OBJECT_ID = COL.OBJECT_ID AND IC.COLUMN_ID = COL.COLUMN_ID ' + CHAR(13)
SET @SQL = @SQL + ' INNER JOIN ' + @DBName + '.SYS.TABLES T ON IND.OBJECT_ID = T.OBJECT_ID ' + CHAR(13)
SET @SQL = @SQL + ' ORDER BY T.NAME, IND.NAME, IND.INDEX_ID, IC.INDEX_COLUMN_ID ' + CHAR(13)

EXEC(@SQL)

INSERT INTO @tblIndexList SELECT IndexName FROM #tblIndexListSingleDB GROUP BY IndexName

SELECT @IndexCount = COUNT(*) FROM @tblIndexList
WHILE @IndexCount > 0
BEGIN

SELECT TOP 1 @IndexName = IndexName FROM @tblIndexList

SELECT @Columns = @Columns + ColumnName + ', ' FROM #tblIndexListSingleDB WHERE IndexName = @IndexName AND IsIncluded != 1
IF LEN(@Columns) > 1
SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)

SELECT @IncludedColumns = @IncludedColumns + ColumnName + ', ' FROM #tblIndexListSingleDB WHERE IndexName = @IndexName AND IsIncluded = 1
IF LEN(@IncludedColumns) > 1
SET @IncludedColumns = LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1)

INSERT INTO #tblIndexListAllDB (DataBaseName, TableName, IndexName, ColumnName, IncludedColumns, IndexType)
SELECT DataBaseName, TableName, IndexName, @Columns, @IncludedColumns, IndexType
FROM  #tblIndexListSingleDB
WHERE IndexName = @IndexName
GROUP BY DataBaseName, TableName, IndexName, IndexType

DELETE FROM @tblIndexList WHERE IndexName = @IndexName
SELECT @IndexCount = COUNT(*) FROM @tblIndexList
SET @Columns = ''
SET @IncludedColumns = ''

END


DELETE FROM @tblDBList WHERE DBName =  @DBName
SELECT @DBCount = COUNT(*) FROM @tblDBList
SET @SQL = ''
DELETE FROM #tblIndexListSingleDB




END

SELECT * FROM #tblIndexListAllDB
DROP TABLE #tblIndexListSingleDB
DROP TABLE #tblIndexListAllDB