One difference between OBJECT_ID() and OBJECT_NAME() in SQL Server is the syntax used for cross-database queries. By this I mean, when they’re used on objects in a different database.

The OBJECT_NAME() function has an optional argument that you can provide, which specifies the database ID of the database that contains the object you’re trying to get the name of. Providing this argument enables you to get the name of an object on a different database.

The OBJECT_ID() function on the other hand, doesn’t require such an argument. Instead, this function allows you to use a 3-part name to specify the database, schema, and name of the object that you’re trying to get the ID of.

This article contains examples of using OBJECT_ID() to get the name of an object from a different database.

Example 1 – Basic Query

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

USE WideWorldImportersDW;
SELECT OBJECT_ID('Music.dbo.Artists') AS Result;

Result:

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

Here, I switched to the WideWorldImportersDW database, then queried the name of an object on the Music database.

Example 2 – Compared with the Current Database

By default, SQL Server assumes that the object name is in the context of the current database. So if you don’t use a 3 part name to specify an object in a different database, the SQL Server Database Engine will only look in the current database.

Here’s the same code from the previous example, except this time I include 1 part and 2 part names. Also, 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 current database is Music, and that’s where the object resides.

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.

If both databases had a schema-scoped object called Artists then we might’ve gotten a different result. In such cases, it would be easy to mistakenly assume that the result is correct when in fact it’s not.