Metadata COL_NAME SQL function

1. Usage of COL_NAME to get the name of the column at position 2 in customers table

SQL Server Query 1

            
 SELECT COL_NAME(OBJECT_ID('Customers'), 2) AS ColumnName;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Column(new COL_NAME(new OBJECT_ID("Customers"), 2), "ColumnName")  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT COL_NAME(OBJECT_ID(@pMAIN_2507200150427183860), @pMAIN_2507200150427183861) AS ColumnName;


            
        

Parameters (If used)

Query Results 1:

2. Usage of COL_NAME o find the column_id for a specific column in a customer table

SQL Server Query 2

            
 SELECT column_id AS Column_ID  

 FROM sys.columns  

 WHERE object_id = OBJECT_ID('Customers')  

   AND name = 'ContactName';  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Column("column_id", "Column_ID")  

     .From("sys.columns")  

     .Where(new Where(new Column("object_id").Equale(new OBJECT_ID("customers")))  

         .AND(new Column("name").Equale("ContactName"))  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT column_id AS Column_ID
FROM sys.columns
WHERE object_id = OBJECT_ID(@pMAIN_2507200150427218150)
      AND name = @pMAIN_2507200150427218151;


            
        

Parameters (If used)

Query Results 2:

3. Usage of STATS_DATE the Names of All Columns in the Orders Table

SQL Server Query 3

            
 SELECT  

     c.name AS ColumnName,  

     COL_NAME(t.object_id, c.column_id) AS ColumnNameFromFunction  

 FROM sys.tables t  

 INNER JOIN sys.columns c ON t.object_id = c.object_id  

 WHERE t.name = 'Orders'  

 ORDER BY c.column_id;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Column("c.name", "ColumnName")  

     .Column(new COL_NAME(new Column("t.object_id"), new Column("c.column_id")), "ColumnNameFromFunction")  

     .From("sys.tables", "t")  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("sys.columns", "c")  

             .On(new Column("t.object_id").Equale(new Column("c.object_id")))  

     })  

     .Where(new Where(new Column("t.name").Equale("Orders")))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT c.name AS ColumnName,
       COL_NAME(t.object_id, c.column_id) AS ColumnNameFromFunction
FROM sys.tables AS t
     INNER JOIN
     sys.columns AS c
     ON t.object_id = c.object_id
WHERE t.name = @pMAIN_2507200150427388890;


            
        

Parameters (If used)

Query Results 3: