SQLDBA- Get the list of indices on table with columns and include columns

 

Below help us to get the list of indices  on  table with columns and include columns also.

/*

Explanation:

sys.indexes: Contains information about all indexes in the database.

sys.index_columns: Links indexes to their columns and indicates whether a column is included (is_included_column = 1).

sys.columns: Provides column names for the table.

OBJECT_ID('YourTableName'): Filters the query to only the specified table.

Replace 'YourTableName' with the name of your table to get the desired results. This query will list all indexes, their columns, and whether each column is an included column.

*/

USE <Db Name>

GO

SELECT 

   i.name AS IndexName,

    i.type_desc AS IndexType,

    c.name AS ColumnName,

    ic.is_included_column AS IsIncludedColumn

    --ic.*

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

WHERE 

    i.object_id = OBJECT_ID('sls_order_master_extension_closed_order') -- Replace 'YourTableName' with your table name

ORDER BY 

    i.name, ic.is_included_column DESC, ic.index_column_id;

    ----ix_sls_order_master_extension_closed_order_4

    --SELECT TOP(1)* FROM reference.dbo.company_master 

    --SELECT * FROM  sys.index_columns