We can write a query like below to get the name of all the indexes that have included columns in it and the name of the table to which the index belongs to:
SELECT DISTINCT T.Name 'Table Name', I.Name 'Index Name', I.type_desc 'Index Type', C.Name 'Included Column Name' FROM sys.indexes I INNER JOIN sys.index_columns IC ON I.object_id = IC.object_id AND I.index_id = IC.index_id INNER JOIN sys.columns C ON IC.object_id = C.object_id and IC.column_id = C.column_id INNER JOIN sys.tables T ON I.object_id = T.object_id WHERE is_included_column = 1 ORDER BY T.Name, I.Name
Let us see this with an example:
CREATE DATABASE SqlHintsDemoDB GO USE SqlHintsDemoDB GO /*Let us create a Table with a primary key column having a clustered index, a non clustered index with included columns and a non clustered index without any included columns.*/ CREATE TABLE dbo.TblIndexWithIncludedColumn ( Col1 int IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL , Col2 int NOT NULL , Col3 int NOT NULL , Col4 int NOT NULL ) GO CREATE NONCLUSTERED INDEX IX_IndexWithIncludedColumn ON dbo.TblIndexWithIncludedColumn(Col2) INCLUDE (Col3) GO CREATE NONCLUSTERED INDEX IX_IndexWithoutIncludedColumn1 ON dbo.TblIndexWithIncludedColumn (Col4) GO /* Let us create one more Table with clustered and non clustered index without any included columns in it. */ CREATE TABLE dbo.TblIndexWithOutIncludedColumn ( C1 int IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL , C2 int NOT NULL) GO CREATE NONCLUSTERED INDEX IX_IndexWithoutIncludedColumn1 ON dbo.TblIndexWithOutIncludedColumn(C2) GO
Now let us run the query to get the list of all the indexes that have included columns in it and the name of the table to which the index belongs to and verify the result:
Great script! Thanks. Saved me time!
Thank you SO much, I had been trying to search fro some queries which could fetch me the Index on a particular table with INCLUDED COLUMNS name and yours suggestion is the one, which workd for me… Thank you so much!!
In other’s blogs, the queries were too huge and seemed very complex and unnecessary.
Thank you Svik… Appreciate your comments…