SQL Server - Display Indexes With Their Columns & Included Columns

Display SQL Server Index Details – Type, Key Columns, Included Columns

Have you ever wanted a query that would give you a list of all the indexes that exist in your database along with details of the index type and all the columns that are a part of the index key and all included columns?

Well have a look at this query. It might help:

--Display all indexes along with key columns, included columns and index type

DECLARE @TempTable AS TABLE (SchemaName VARCHAR(100), 
							 ObjectID INT, 
							 TableName VARCHAR(100), 
							 IndexID INT, 
							 IndexName VARCHAR(100), 
							 ColumnID INT, 
							 column_index_id INT, 
							 ColumnNames  VARCHAR(500), 
							 IncludeColumns  VARCHAR(500), 
							 NumberOfColumns INT, 
							 IndexType  VARCHAR(20),
							 LastColRecord INT);

WITH CTE_Indexes (SchemaName, ObjectID, TableName, IndexID, IndexName, ColumnID, column_index_id, ColumnNames, IncludeColumns, NumberOfColumns, IndexType)
AS
(
SELECT s.name, t.object_id, t.name, i.index_id, i.name, c.column_id, ic.index_column_id,
		CASE ic.is_included_column WHEN 0 THEN CAST(c.name AS VARCHAR(5000)) ELSE '' END, 
		CASE ic.is_included_column WHEN 1 THEN CAST(c.name AS VARCHAR(5000)) ELSE '' END, 1, i.type_desc
	FROM  sys.schemas AS s
		JOIN sys.tables AS t ON s.schema_id = t.schema_id
			JOIN sys.indexes AS i ON i.object_id = t.object_id
				JOIN sys.index_columns AS ic ON ic.index_id = i.index_id AND ic.object_id = i.object_id
					JOIN sys.columns AS c ON c.column_id = ic.column_id AND c.object_id = ic.object_id
						AND ic.index_column_id = 1
UNION ALL
SELECT s.name, t.object_id, t.name, i.index_id, i.name, c.column_id, ic.index_column_id,
		CASE ic.is_included_column WHEN 0 THEN CAST(cte.ColumnNames + ', ' + c.name AS VARCHAR(5000))  ELSE cte.ColumnNames END, 
		CASE  
			WHEN ic.is_included_column = 1 AND cte.IncludeColumns != '' THEN CAST(cte.IncludeColumns + ', ' + c.name AS VARCHAR(5000))
			WHEN ic.is_included_column =1 AND cte.IncludeColumns = '' THEN CAST(c.name AS VARCHAR(5000)) 
			ELSE '' 
		END,
		cte.NumberOfColumns + 1, i.type_desc
	FROM  sys.schemas AS s
		JOIN sys.tables AS t ON s.schema_id = t.schema_id
			JOIN sys.indexes AS i ON i.object_id = t.object_id
				JOIN sys.index_columns AS ic ON ic.index_id = i.index_id AND ic.object_id = i.object_id
					JOIN sys.columns AS c ON c.column_id = ic.column_id AND c.object_id = ic.object_id 
					JOIN CTE_Indexes cte ON cte.Column_index_ID + 1 = ic.index_column_id  
					--JOIN CTE_Indexes cte ON cte.ColumnID + 1 = ic.index_column_id  
							AND cte.IndexID = i.index_id AND cte.ObjectID = ic.object_id

)
INSERT INTO  @TempTable 
SELECT *, RANK() OVER (PARTITION BY ObjectID, IndexID ORDER BY NumberOfColumns DESC) AS LastRecord FROM CTE_Indexes AS cte;

SELECT SchemaName, TableName, IndexName, ColumnNames, IncludeColumns, IndexType FROM @TempTable
WHERE LastColRecord = 1
ORDER BY objectid, TableName, indexid, IndexName

The following screen shot shows some sample output when this query is run against the AdventureWorks2014 sample database:

We can see that there is an index called [IX_ProductReview_ProductID_Name] on the table called [Production].[ProductReview]. The query output shows that this is a non clustered composite index based on the ProductID and ReviewerName columns and with an included column called Comments.

Here is the CREATE INDEX script generated from this index:

CREATE NONCLUSTERED INDEX [IX_ProductReview_ProductID_Name] ON [Production].[ProductReview]
(
	[ProductID] ASC,
	[ReviewerName] ASC
)
INCLUDE ( 	[Comments]) 
WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, 
ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON)
GO

If you would like to learn more about SQL Server indexing why not take a look at our SQL Server Performance & Tuning training course. It covers plenty of interesting things about indexing:

• Indexing: OLTP, OLAP, Hybrid 
• Index Optimisation
• Indexing: UNIQUE Index, DUPLICATE Index, NONCLUSTERED Index, CLUSTERED Index, Covering Index, Composite Index, INCLUDE Index, Filtered Indexes
• Creating Indexes
• Index Structure: B-Tree Index, Non-clustered B-Tree, Clustered B-Tree
• Non-Clustered Index on a Table Containing Clustered Index
• The sys.indexes View
• When To Index
• The Query Optimizer
• Index Tuning
• A Few Tips: Don’t Over Index, Composite Indexes, Large Data Types, Be Selective, Optimize, Clustered Indexes, Targets For Index Tuning
• Growing Indexes
• FILLFACTOR: Choosing the FILLFACTOR, PADINDEX
• The Database Engine Tuning Advisor: Using A Workload File,  Using A Selected Query In Query Analyzer, Saving An Index Tuning Wizard Script, Accepting Recommendations
• Monitoring Index Usage
• Table Hints
• Indexed Views
• Displaying Information About Indexes: Viewing Index Records, Viewing Pages
• Index Depth & Density: DBCC SHOWCONTIG, sys.dm_db_index_physical_stats Function
• Defragmenting/Rebuilding An Index
• Online Index Building
• Index Usage
• Dynamic Management Views & Functions for Indexes: sys.dm_db_index_physical_stats, sys.dm_db_index_operational_stats, sys.dm_db_index_usage_stats