Get the list of all indexes and index columns in a Database

Get the list of all indexes and index columns in a database

by

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
Get the list of all indexes and index columns in a Database

Related Articles

Reference


1 thought on “Get the list of all indexes and index columns in a database”

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.