Wednesday, October 19, 2011

SQL Server 2008 : Query to get list of Indexes from the single Database

This script will provide 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) from a given database.


USE <Database Name>
GO

DECLARE
@IndexCount INT,
@Columns varchar(2000) = '',
@IncludedColumns varchar(2000) = '',
@IndexName VARCHAR(1000) = ''


DECLARE @tblIndex TABLE (IndexName VARCHAR(1000), ColumnName VARCHAR(100), TableName VARCHAR(100), IndexType VARCHAR(100) , IsIncluded BIT)
DECLARE @tblIndexDetail TABLE (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 @tblIndex (IndexName, ColumnName, TableName, IndexType, IsIncluded)
SELECT IND.NAME AS IndexName, COL.NAME AS ColumnName, T.Name AS TableName, IND.TYPE_DESC AS IndexType , IC.IS_INCLUDED_COLUMN AS IsIncluded
FROM SYS.INDEXES IND
INNER JOIN SYS.INDEX_COLUMNS IC ON IND.OBJECT_ID = IC.OBJECT_ID AND IND.INDEX_ID = IC.INDEX_ID
INNER JOIN SYS.COLUMNS COL ON IC.OBJECT_ID = COL.OBJECT_ID AND IC.COLUMN_ID = COL.COLUMN_ID
INNER JOIN SYS.TABLES T ON IND.OBJECT_ID = T.OBJECT_ID
ORDER BY T.NAME, IND.NAME, IND.INDEX_ID, IC.INDEX_COLUMN_ID


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

SELECT TOP 1 @IndexName = IndexName FROM @tblIndex

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

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

INSERT INTO @tblIndexDetail (DataBaseName, TableName, IndexName, ColumnName, IncludedColumns, IndexType)
SELECT DB_Name() AS DatabaseName, TableName, IndexName, @Columns, @IncludedColumns, IndexType
FROM  @tblIndex
WHERE IndexName = @IndexName
GROUP BY TableName, IndexName, IndexType

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

END

SELECT * FROM @tblIndexDetail
GO

No comments:

Post a Comment