Metadata OBJECT_NAME SQL function

1. Usage of OBJECT_NAME

SQL Server Query 1

            
 SELECT DISTINCT OBJECT_NAME(object_id) AS Schema_Name   

 FROM master.sys.objects    

 UNION ALL  

 SELECT DISTINCT OBJECT_NAME(object_id, 1) AS Schema_Name    

 FROM master.sys.objects;

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select().Distinct()  

     .Column(new OBJECT_NAME(new Column("object_id")), "Schema_Name")  

     .From("master.sys.objects")  

     .UnionAll(new SqlQueryBuilder()  

         .Select().Distinct()  

         .Column(new OBJECT_NAME(new Column("object_id"), 1), "Schema_Name")  

         .From("master.sys.objects")  

     )  

     .Build();

Query build by SqlQueryBuilder 1

            
SELECT DISTINCT OBJECT_NAME(object_id) AS Schema_Name
FROM master.sys.objects
UNION ALL
SELECT DISTINCT OBJECT_NAME(object_id, @pMAIN_2507192027001779790) AS Schema_Name
FROM master.sys.objects;

Parameters (If used)

Name Value
@pMAIN_2507192027001779790 1

Query Results 1:

  Schema_Name
1
2 spt_fallback_db
3 spt_fallback_dev
4 spt_fallback_usg
5 spt_monitor
6 spt_values

2. Usage of OBJECT_NAME

SQL Server Query 2

            
 SELECT QUOTENAME(DB_NAME(db_id()))     

     + N'.'     

     + QUOTENAME(OBJECT_NAME(object_id, db_id())) AS Full_Name   

 FROM sys.objects;

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Column(new CONCAT(new QUOTENAME(new DB_NAME(new DB_ID())),  

         ".",  

         new QUOTENAME(new OBJECT_NAME(new Column("object_id"), new DB_ID()))), "Full_Name")  

     .From("sys.objects")  

     .Build();

Query build by SqlQueryBuilder 2

            
SELECT CONCAT(QUOTENAME(DB_NAME(DB_ID()), @pMAIN_2507192027002507410), @pMAIN_2507192027002507411, QUOTENAME(OBJECT_NAME(object_id, DB_ID()), @pMAIN_2507192027002507412)) AS Full_Name
FROM sys.objects;

Parameters (If used)

Name Value
@pMAIN_2507192027002507410 [
@pMAIN_2507192027002507411 .
@pMAIN_2507192027002507412 [

Query Results 2:

  Full_Name
1 [TheBlueSoftDB].[PK_Categories]
2 [TheBlueSoftDB].[Products]
3 [TheBlueSoftDB].[PK_Products]
4 [TheBlueSoftDB].[Alphabetical list of products]
5 [TheBlueSoftDB].[Current Product List]
6 [TheBlueSoftDB].[Orders]
7 [TheBlueSoftDB].[PK_Orders]
8 [TheBlueSoftDB].[CustomerProductSalesIndexed]
9 [TheBlueSoftDB].[Orders Qry]
10 [TheBlueSoftDB].[Products Above Average Price]
11 [TheBlueSoftDB].[Products by Category]
12 [TheBlueSoftDB].[Quarterly Orders]
13 [TheBlueSoftDB].[Employees]
14 [TheBlueSoftDB].[PK_Employees]
15 [TheBlueSoftDB].[Shippers]
16 [TheBlueSoftDB].[PK_Shippers]
17 [TheBlueSoftDB].[user_feedbacks]
18 [TheBlueSoftDB].[Order Details]
19 [TheBlueSoftDB].[PK_user_feedbacks]
20 [TheBlueSoftDB].[PK_Order_Details]
21 [TheBlueSoftDB].[DF_user_feedbacks_entered_on]
22 [TheBlueSoftDB].[Invoices]
23 [TheBlueSoftDB].[Order Details Extended]
24 [TheBlueSoftDB].[Order Subtotals]
25 [TheBlueSoftDB].[Product Sales for 1997]
26 [TheBlueSoftDB].[Category Sales for 1997]
27 [TheBlueSoftDB].[Sales by Category]
28 [TheBlueSoftDB].[Sales Totals by Amount]
29 [TheBlueSoftDB].[Summary of Sales by Quarter]
30 [TheBlueSoftDB].[Summary of Sales by Year]
31 [TheBlueSoftDB].[CustomerCustomerDemo]
32 [TheBlueSoftDB].[CustomerDemographics]
33 [TheBlueSoftDB].[EmployeeTerritories]
34 [TheBlueSoftDB].[Region]
35 [TheBlueSoftDB].[Territories]
36 [TheBlueSoftDB].[PK_CustomerCustomerDemo]
37 [TheBlueSoftDB].[PK_CustomerDemographics]
38 [TheBlueSoftDB].[PK_EmployeeTerritories]
39 [TheBlueSoftDB].[PK_Region]
40 [TheBlueSoftDB].[PK_Territories]
41 [TheBlueSoftDB].[DF_Order_Details_UnitPrice]
42 [TheBlueSoftDB].[DF_Order_Details_Quantity]
43 [TheBlueSoftDB].[DF_Order_Details_Discount]
44 [TheBlueSoftDB].[DF_Orders_Freight]
45 [TheBlueSoftDB].[DF_Products_UnitPrice]
46 [TheBlueSoftDB].[DF_Products_UnitsInStock]
47 [TheBlueSoftDB].[DF_Products_UnitsOnOrder]
48 [TheBlueSoftDB].[DF_Products_ReorderLevel]
49 [TheBlueSoftDB].[DF_Products_Discontinued]
50 [TheBlueSoftDB].[FK_CustomerCustomerDemo]
51 [TheBlueSoftDB].[FK_CustomerCustomerDemo_Customers]
52 [TheBlueSoftDB].[FK_Employees_Employees]
53 [TheBlueSoftDB].[FK_EmployeeTerritories_Employees]
54 [TheBlueSoftDB].[FK_EmployeeTerritories_Territories]
55 [TheBlueSoftDB].[FK_Order_Details_Orders]
56 [TheBlueSoftDB].[FK_Order_Details_Products]
57 [TheBlueSoftDB].[FK_Orders_Customers]
58 [TheBlueSoftDB].[FK_Orders_Employees]
59 [TheBlueSoftDB].[FK_Orders_Shippers]
60 [TheBlueSoftDB].[FK_Products_Categories]
61 [TheBlueSoftDB].[FK_Products_Suppliers]
62 [TheBlueSoftDB].[FK_Territories_Region]
63 [TheBlueSoftDB].[CK_Birthdate]
64 [TheBlueSoftDB].[CK_Discount]
65 [TheBlueSoftDB].[CK_Quantity]
66 [TheBlueSoftDB].[CK_UnitPrice]
67 [TheBlueSoftDB].[CK_Products_UnitPrice]
68 [TheBlueSoftDB].[CK_ReorderLevel]
69 [TheBlueSoftDB].[CK_UnitsInStock]
70 [TheBlueSoftDB].[CK_UnitsOnOrder]
71 [TheBlueSoftDB].[CustOrderHist]
72 [TheBlueSoftDB].[CustOrdersDetail]
73 [TheBlueSoftDB].[CustOrdersOrders]
74 [TheBlueSoftDB].[Employee Sales by Country]
75 [TheBlueSoftDB].[Sales by Year]
76 [TheBlueSoftDB].[SalesByCategory]
77 [TheBlueSoftDB].[Ten Most Expensive Products]
78 [TheBlueSoftDB].[Customer and Suppliers by City]
79 [TheBlueSoftDB].[Ten Most Expensive Products]
80 [TheBlueSoftDB].[Employee Sales by Country]
81 [TheBlueSoftDB].[Sales by Year]
82 [TheBlueSoftDB].[CustOrdersDetail]
83 [TheBlueSoftDB].[CustOrdersOrders]
84 [TheBlueSoftDB].[CustOrderHist]
85 [TheBlueSoftDB].[SalesByCategory]
86 [TheBlueSoftDB].[sp_Employees_Insert]
87 [TheBlueSoftDB].[sp_Employees_Update]
88 [TheBlueSoftDB].[sp_Employees_SelectAll]
89 [TheBlueSoftDB].[sp_Employees_SelectRow]
90 [TheBlueSoftDB].[DateOnly]
91 [TheBlueSoftDB].[MyRound]
92 [TheBlueSoftDB].[sp_Employees_Cursor]
93 [TheBlueSoftDB].[sp_employees_rownum]
94 [TheBlueSoftDB].[sp_employees_rank]
95 [TheBlueSoftDB].[sp_employees_rollup]
96 [TheBlueSoftDB].[QueryNotificationErrorsQueue]
97 [TheBlueSoftDB].[queue_messages_1977058079]
98 [TheBlueSoftDB].[EventNotificationErrorsQueue]
99 [TheBlueSoftDB].[queue_messages_2009058193]
100 [TheBlueSoftDB].[ServiceBrokerQueue]
101 [TheBlueSoftDB].[Customers]
102 [TheBlueSoftDB].[queue_messages_2041058307]
103 [TheBlueSoftDB].[PK_Customers]
104 [TheBlueSoftDB].[Suppliers]
105 [TheBlueSoftDB].[PK_Suppliers]
106 [TheBlueSoftDB].[Customer and Suppliers by City]
107 [TheBlueSoftDB].[Categories]

3. Usage of OBJECT_NAME in WHERE clause

SQL Server Query 3

            
 SELECT name AS Name, object_id AS Object_ID, type_desc AS Type_Desc  

 FROM sys.objects    

 WHERE name = OBJECT_NAME(7);

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Column("name", "Name")  

     .Column("object_id", "Object_ID")  

     .Column("type_desc", "Type_Desc")  

     .From("sys.objects")  

     .Where(new Where(new Column("name").Equale(new OBJECT_NAME(7))))  

     .Build();

Query build by SqlQueryBuilder 3

            
SELECT name AS Name,
       object_id AS Object_ID,
       type_desc AS Type_Desc
FROM sys.objects
WHERE name = OBJECT_NAME(@pMAIN_2507192027003059260);

Parameters (If used)

Name Value
@pMAIN_2507192027003059260 7

Query Results 3:

  Name Object_ID Type_Desc