Get an Object’s ID from its Name in SQL Server: OBJECT

In SQL Server, you can use the OBJECT_ID() function to return an object’s ID, based on its name.

This can be useful when you need an object’s ID, but you only know its name.

The official definition of OBJECT_ID() is that it returns the database object identification number of a schema-scoped object.

Example 1 – Basic Usage

Here’s a basic example to demonstrate how it works.

SELECT OBJECT_ID('Artists') AS Result;

Result:

+-----------+
| Result    |
|-----------|
| 885578193 |
+-----------+

In this case, the current database does contain an object called Albums, and its ID is 885578193. This is the ID that you can find in the object_id column of the sys.objects system catalog view.

Example 2 – Check the sys.objects View

Here’s another basic example to verify what I just said.

SELECT 
  name,
  object_id,
  OBJECT_ID(name) AS [OBJECT_ID(name)]
FROM sys.objects
WHERE name = 'Artists';

Result:

+---------+-------------+-------------------+
| name    | object_id   | OBJECT_ID(name)   |
|---------+-------------+-------------------|
| Artists | 885578193   | 885578193         |
+---------+-------------+-------------------+

The sys.objects system catalog view contains a row for each user-defined, schema-scoped object that is created within a database.

In this example, the first two columns display the object’s name and object_id respectively.

In the third column of this example, I use OBJECT_ID() to return the object’s ID based on its name. To do this, I pass the name column to the OBJECT_ID() function.

This is obviously just an example, and using OBJECT_ID() was unnecessary in this case, because sys.objects already returns the object’s ID.

Example 3 – A More Useful Example

In this example, I use OBJECT_ID() in a WHERE clause so that I only get results related to the table called Client.

SELECT
    OBJECT_NAME(referencing_id) AS [Referencing Entity],
    OBJECT_NAME(referencing_minor_id) AS [Referencing Minor Entity],
    referencing_class_desc AS [Class],
    COL_NAME(referenced_id, referenced_minor_id) AS [Column]
FROM sys.sql_expression_dependencies
WHERE referenced_id = OBJECT_ID('Client');

Result:

+----------------------+----------------------------+------------------+------------+
| Referencing Entity   | Referencing Minor Entity   | Class            | Column     |
|----------------------+----------------------------+------------------+------------|
| uspGetClient         | NULL                       | OBJECT_OR_COLUMN | NULL       |
| uspGetOrdersByClient | NULL                       | OBJECT_OR_COLUMN | NULL       |
| chkClientCode        | NULL                       | OBJECT_OR_COLUMN | ClientCode |
+----------------------+----------------------------+------------------+------------+

In this case I wanted to see which entities depend on the Client table (i.e. which entities reference that table in their SQL code). The referenced_id column uses the object’s ID, so by using OBJECT_ID(), I was able to get the ID of the Client table and compare it to referenced_id.

See Find Dependencies in SQL Server: sql_expression_dependencies for a more detailed explanation of this query, and related examples.

Example 4 – Fully Qualified Names

You also have the option of qualifying the object name with the schema name, and also the database name if so desired.

Here’s a simple example to demonstrate:

SELECT 
  OBJECT_ID('Artists') AS [1 Part Name],
  OBJECT_ID('dbo.Artists') AS [2 Part Name],
  OBJECT_ID('Music.dbo.Artists') AS [3 Part Name];

Result:

+---------------+---------------+---------------+
| 1 Part Name   | 2 Part Name   | 3 Part Name   |
|---------------+---------------+---------------|
| 885578193     | 885578193     | 885578193     |
+---------------+---------------+---------------+

Here it is again, this time using square brackets as delimiters:

SELECT 
  OBJECT_ID('[Artists]') AS [1 Part Name],
  OBJECT_ID('[dbo].[Artists]') AS [2 Part Name],
  OBJECT_ID('[Music].[dbo].[Artists]') AS [3 Part Name];

Result:

+---------------+---------------+---------------+
| 1 Part Name   | 2 Part Name   | 3 Part Name   |
|---------------+---------------+---------------|
| 885578193     | 885578193     | 885578193     |
+---------------+---------------+---------------+

Same result.

If you ever get a NULL result even though you know the object exists, try qualifying it with the schema (and even the database name).

Example 5 – Cross Database Queries

By default, SQL Server assumes that the object name is in the context of the current database. You can use a 3 part name to specify an object in a different database.

Here’s the same code from the previous example, except this time I run the code twice: the first time it’s run in the Music database, the second time it’s run in the WideWorldImportersDW database:

USE Music;
SELECT 
  OBJECT_ID('Artists') AS [1 Part Name],
  OBJECT_ID('dbo.Artists') AS [2 Part Name],
  OBJECT_ID('Music.dbo.Artists') AS [3 Part Name];

USE WideWorldImportersDW;
SELECT 
  OBJECT_ID('Artists') AS [1 Part Name],
  OBJECT_ID('dbo.Artists') AS [2 Part Name],
  OBJECT_ID('Music.dbo.Artists') AS [3 Part Name];

Result:

Changed database context to 'Music'.
+---------------+---------------+---------------+
| 1 Part Name   | 2 Part Name   | 3 Part Name   |
|---------------+---------------+---------------|
| 885578193     | 885578193     | 885578193     |
+---------------+---------------+---------------+
(1 row affected)
Changed database context to 'WideWorldImportersDW'.
+---------------+---------------+---------------+
| 1 Part Name   | 2 Part Name   | 3 Part Name   |
|---------------+---------------+---------------|
| NULL          | NULL          | 885578193     |
+---------------+---------------+---------------+
(1 row affected)

In the first result, all three columns return the correct ID. This is because the object happens to be in the Music database.

In the second result, only the 3 part name is able to find the correct object. This is to be expected, because the 1 part and 2 part names don’t specify the name of the database, therefore it assumes the object is in the WideWorldImportersDW (wrong) database.

Example 6 – Specify the Object Type

The OBJECT_ID() function also accepts an argument for the object type. This argument, if provided, comes after the object’s name.

Example:

SELECT 
  OBJECT_ID('Artists', 'U') AS [Table];

Result:

+-----------+
| Table     |
|-----------|
| 885578193 |
+-----------+

Here, I specify that the object type is U, which means “Table (user-defined)”.

If I try to specify a different object type, I get NULL:

SELECT 
  OBJECT_ID('Artists', 'U') AS [Table],
  OBJECT_ID('Artists', 'V') AS [View],
  OBJECT_ID('Artists', 'P') AS [Stored Procedure];

Result:

+-----------+--------+--------------------+
| Table     | View   | Stored Procedure   |
|-----------+--------+--------------------|
| 885578193 | NULL   | NULL               |
+-----------+--------+--------------------+

Here it is again, but with the name of a view instead:

SELECT 
  OBJECT_ID('RockAlbums', 'U') AS [Table],
  OBJECT_ID('RockAlbums', 'V') AS [View],
  OBJECT_ID('RockAlbums', 'P') AS [Stored Procedure];

Result:

+---------+------------+--------------------+
| Table   | View       | Stored Procedure   |
|---------+------------+--------------------|
| NULL    | 1525580473 | NULL               |
+---------+------------+--------------------+

Database.Guide

Get an Object’s ID from its Name in SQL Server: OBJECT_ID()

Example 1 – Basic Usage

Example 2 – Check the sys.objects View

Example 3 – A More Useful Example

Example 4 – Fully Qualified Names

Example 5 – Cross Database Queries

Example 6 – Specify the Object Type