Object_Id Metadata Function

Object_ID is a SQL Server Metadata function which returns the database object identification number of a schema-scoped object.

• It can not be used for object that are not schema-scoped such as DDL triggers.
• If any error occurred it returns null.

SYNTAX

OBJECT_ID ( 'database_name . schema_name . object_name' )

Object_name is the name of object to be used.

database_name and schema_name is the database and schema name of specified object.  Specifying database name and schema name is an optional.

Lets look at an example of using OBJECT_ID function in SQL Server.

Using OBJECT_ID function

Following T-SQL Query, uses the OBJECT_ID metadata function and returns object Id of the table Employee in the database AdventureWorks2017.

USE Master; 
GO 
SELECT OBJECT_ID(N'AdventureWorks2017.HumanResources.Employee') AS 'Object ID'; 
GO

You can also remove database name Adventurework2017 from above T-SQL Query,  if the Adventureworks2017 database is already the active database.

Lets remove the database name from from above T-SQL Query as following.

USE AdventureWorks2017; 
GO 
SELECT OBJECT_ID(N'HumanResources.Employee') AS 'Object ID'; 
GO

Checking the existence of Objects in database using OBJECT_ID function

Using OBJECT_ID metadata function, you can check the existence of objects in database such as any table exists in database or not.

Following T-SQL query uses OBJECT_ID metadata function to check the existence of table Employee in database Adventureworks2017.

USE AdventureWorks2017; 
GO 
IF OBJECT_ID (N'HumanResources.Employee', N'U') IS NOT NULL 
  BEGIN
  SELECT 'Table exits' AS [Status]
  END
ELSE
  BEGIN
  SELECT 'Table does not exist' AS [Status]
  END

You can also verify the object Id returned by OBJECT_ID function in SQL Server system catalog view sys.objects which contains a row for each user-defined, schema-scoped object that is created within a database.

Lets see the details of object Employee whose object Id is 893581784 which is returned by OBJECT_ID function.

SELECT * FROM Sys.Objects 
WHERE OBJECT_ID =1893581784

You can see, using object id you can find more details about object in system catalog view sys.objects.

View all the available Metadata function in SQL Server Management Studio

You can see all the available metadata function in SQL Server using SQL Server Management studio.

Go to Object Explorer, In Database, expand the Programmability after that expand the Functions.

Inside functions you can see the list of available Metadata functions.

Also Read..

PARSENAME

Check if User table or Temp table exists in database

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 374 total views,  1 views today