Total Page Preview:   000000009376

SQL SERVER- Script to find or get all indexes on table with columns name in the database

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 :
Get All Indexes in table From Database

 

Thanks !!!

Thank You

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



Comments


                           
                           

                           

                           

Facebook User: