Tuesday, October 18, 2011

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


One task of the DBAs is to keep the eye on the index as monitoring to duplicate Index and unused index are required to remove from the database. Indexes helps to retrieve query result faster but too many indexes can affect the DML commands (INSERT/ UPDATE/ DELETE) badly. So there should be a balance while creating indexes.
Below query will return the list of Indexes with Database Name, Table Name, Index Name & Column Name, Type of Index (Clustered or Non Clustered), IsIncluded flag (this is applicable for covering index).

SELECT DB_NAME() AS DataBaseName, IND.NAME AS IndexName, COL.NAME AS ColumnName, OBJECT_NAME(IND.OBJECT_ID) 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
--WHERE IND.IS_PRIMARY_KEY = 0 AND IND.IS_UNIQUE = 0 AND IND.IS_UNIQUE_CONSTRAINT = 0 AND T.IS_MS_SHIPPED = 0
ORDER BY T.NAME, IND.NAME, IND.INDEX_ID, IC.INDEX_COLUMN_ID

No comments:

Post a Comment