Getting the list of all indexes and index columns in a database is quiet simple by using the sys.indexes and sys.index_columns system catalog views. Using sys.indexes you can get all the indexes of tables or views or table valued functions. Coupling sys.indexes with sys.index_columns gives you the name of the column or columns the index was created or included. This will be helpful to see the column names along with the index name. Here is the script I’m using to get the list of all indexes and the columns used in the indexes.
SELECT ix.name as [IndexName], tab.name as [Table Name], COL_NAME(ix.object_id, ixc.column_id) as [Column Name], ix.type_desc, ix.is_disabled FROM sys.indexes ix INNER JOIN sys.index_columns ixc ON ix.object_id = ixc.object_id and ix.index_id = ixc.index_id INNER JOIN sys.tables tab ON ix.object_id = tab.object_id WHERE ix.is_primary_key = 0 /* Remove Primary Keys */ AND ix.is_unique = 0 /* Remove Unique Keys */ AND ix.is_unique_constraint = 0 /* Remove Unique Constraints */ AND tab.is_ms_shipped = 0 /* Remove SQL Server Default Tables */ ORDER BY ix.name, tab.name GO
- More about sys.index_columns at Microsoft Docs.
1 thought on “Get the list of all indexes and index columns in a database”
how to get list of indexes as well as which procedure code need index by T-SQL query