How to find all the indexes that have included columns in it and the name of the table to which the index belongs to?

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:

How_To_Find_All_Indexes_With_Included_Column

3 thoughts on “How to find all the indexes that have included columns in it and the name of the table to which the index belongs to?

  1. 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.

Leave a Reply

Your email address will not be published.