In this article i am goint to explain write a Scrits to find all the indexes that have included columns in it and the name of the table to which the index belongs to
----------------------Get all indexes from database------------------------------------------
SELECT
S.NAME AS 'SchemaName',
T.NAME AS 'TableName',
I.NAME AS 'IndexName',
C.NAME AS 'ColumnName',
i.type_desc AS 'IndexType',
CASE WHEN I.is_primary_key = 1 THEN 'Yes' ELSE 'No' END AS 'IsPrimaryKey'
FROM SYS.TABLES AS T
INNER JOIN SYS.SCHEMAS AS S ON T.SCHEMA_ID = S.SCHEMA_ID
INNER JOIN SYS.INDEXES AS I ON I.OBJECT_ID = T.OBJECT_ID
INNER JOIN SYS.INDEX_COLUMNS AS IC ON IC.OBJECT_ID = T.OBJECT_ID
INNER JOIN SYS.COLUMNS AS C ON C.OBJECT_ID = T.OBJECT_ID AND IC.INDEX_ID = I.INDEX_ID
AND IC.COLUMN_ID = C.COLUMN_ID
WHERE 1 = 1 ORDER BY I.NAME
OUTPUT :
About Author
Brijesh Kumar
Database Developer
I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL
Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall
and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com