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.
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.
Check if User table or Temp table exists in database
3,265 total views, 2 views today