Metadata INDEX_COL SQL function

1. Usage of INDEX_COL to show the key columns of all indexes on a Orders table using system views

SQL Server Query 1

            
 SELECT  

     OBJECT_NAME(i.object_id) AS TableName,  

     i.name AS IndexName,  

     ic.key_ordinal,  

     COL_NAME(i.object_id, ic.column_id) AS ColumnName,  

     INDEX_COL('Northwind.dbo.' + OBJECT_NAME(i.object_id), i.index_id, ic.key_ordinal) AS ColumnNameFromFunction  

 FROM sys.indexes i  

 INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id  

 WHERE OBJECT_NAME(i.object_id) = 'Orders'  

 ORDER BY i.name, ic.key_ordinal;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Column(new OBJECT_NAME(new Column("i.object_id")), "TableName")  

     .Column("i.name","IndexName")  

     .Column("ic.key_ordinal", "Key_Ordinal")  

     .Column(new COL_NAME(new Column("i.object_id"), new Column("ic.column_id")), "ColumnName")  

     .Column(new INDEX_COL(  

         new CONCAT("Northwind.dbo.", new OBJECT_NAME(new Column("i.object_id"))),   

         new Column("i.index_id"),   

         new Column("ic.key_ordinal")), "ColumnNameFromFunction")  

     .From("sys.indexes","i")  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("sys.index_columns","ic")  

             .On(new Column("i.object_id").Equale(new Column("ic.object_id")))  

             .AND(new Column("i.index_id").Equale(new Column("ic.index_id")))  

     })  

     .Where(new Where(new OBJECT_NAME(new Column("i.object_id")).Equale("Orders")))  

     .OrderBy(new OrderBy()  

         .SetColumnAscending("i.name")  

         .SetColumnAscending("ic.key_ordinal"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       ic.key_ordinal AS Key_Ordinal,
       COL_NAME(i.object_id, ic.column_id) AS ColumnName,
       INDEX_COL(CONCAT(@pMAIN_2507192027005542500, OBJECT_NAME(i.object_id)), i.index_id, ic.key_ordinal) AS ColumnNameFromFunction
FROM sys.indexes AS i
     INNER JOIN
     sys.index_columns AS ic
     ON i.object_id = ic.object_id
        AND i.index_id = ic.index_id
WHERE OBJECT_NAME(i.object_id) = @pMAIN_2507192027005542501
ORDER BY i.name ASC, ic.key_ordinal ASC;


            
        

Parameters (If used)

Query Results 1: