If you ever need to use the OBJECT_NAME() function to get an object’s name from a different database in SQL Server, you might run into issues if you don’t know how it works.

You probably know that OBJECT_NAME() accepts an object_id argument that tells SQL Server which object to get the name from.

What you may or may not know, is that this function also accepts an optional database_id argument that tells SQL Server which database the object_id belongs to.

By default, SQL Server assumes that object_id is in the context of the current database. In this case, a query that references an object_id in another database will return NULL or (even worse) incorrect results.

Example 1 – Local Query (from Current Database)

First, here’s a local query that returns the object name from the current database:

USE Music;
SELECT
  name AS [Foreign Key],
  OBJECT_NAME(parent_object_id) AS [Parent Object Name],
  OBJECT_NAME(referenced_object_id) AS [Referenced Object Name]
FROM Music.sys.foreign_keys
WHERE name = 'FK_Artists_Country';

Result:

Changed database context to 'Music'.
+--------------------+----------------------+--------------------------+
| Foreign Key        | Parent Object Name   | Referenced Object Name   |
|--------------------+----------------------+--------------------------|
| FK_Artists_Country | Artists              | Country                  |
+--------------------+----------------------+--------------------------+
(1 row affected)

These results are correct.

This is not a cross-database query. This is just an example to show how this function is used when getting an object’s name from the current database.

Example 2 – Cross-Database Query with WRONG RESULTS!

Now, here’s a cross-database query that produces incorrect results.

USE WideWorldImportersDW;
SELECT
  name AS [Foreign Key],
  OBJECT_NAME(parent_object_id) AS [Parent Object Name],
  OBJECT_NAME(referenced_object_id) AS [Referenced Object Name]
FROM Music.sys.foreign_keys
WHERE name = 'FK_Artists_Country';

Result:

Changed database context to 'WideWorldImportersDW'.
+--------------------+----------------------+-----------------------------+
| Foreign Key        | Parent Object Name   | Referenced Object Name      |
|--------------------+----------------------+-----------------------------|
| FK_Artists_Country | CityKey              | PK_Dimension_Payment_Method |
+--------------------+----------------------+-----------------------------+
(1 row affected)

All I did was switch to a different database, then run the same query again.

You’ll notice that my FROM clause uses a three part name in order to specify the name of the database (Music). This allows the correct foreign key to be found. However, this is not enough to prevent issues from occurring.

As it turns out, the WideWorldImportersDW database has objects with the same object_id that are used in the Music database. Only problem is that they’re completely different objects, with different names. So the results in the last two columns are bogus. These are the names of the wrong objects, on the wrong database. My cross-database query has gotten its wires crossed and returned the wrong objects!

This is particularly dangerous, because if I wasn’t paying attention, these results might appear to be OK. After all, I didn’t get an error.

If these object IDs didn’t exist in this database, I probably would’ve gotten a NULL value (which might make it easier to detect that something is wrong with the results).

Either way, the result is simply wrong.

Example 3 – Cross-Database Query with CORRECT Results

To fix the previous example (without changing the current database), we need to supply the ID of the database that we want the object’s name from.

Like this:

USE WideWorldImportersDW;
SELECT
  name AS [Foreign Key],
  OBJECT_NAME(parent_object_id, 5) AS [Parent Object Name],
  OBJECT_NAME(referenced_object_id, 5) AS [Referenced Object Name]
FROM Music.sys.foreign_keys
WHERE name = 'FK_Artists_Country';

Result:

Changed database context to 'WideWorldImportersDW'.
+--------------------+----------------------+--------------------------+
| Foreign Key        | Parent Object Name   | Referenced Object Name   |
|--------------------+----------------------+--------------------------|
| FK_Artists_Country | Artists              | Country                  |
+--------------------+----------------------+--------------------------+
(1 row affected)

Again, just to be clear, the current database is WideWorldImportersDW, but the objects are on a different database called Music, which has a database ID of 5.

Example 4 – How to Get the Database ID

It’s quite likely that you won’t know what the database’s ID is off the top of your head. You’ll probably know the name of the database, but not its ID.

Fortunately you can use the DB_ID() function to return the ID of the database, based on its name.

Therefore, we can modify the previous example as follows:

USE WideWorldImportersDW;
SELECT
  name AS [Foreign Key],
  OBJECT_NAME(parent_object_id, DB_ID('Music')) AS [Parent Object Name],
  OBJECT_NAME(referenced_object_id, DB_ID('Music')) AS [Referenced Object Name]
FROM Music.sys.foreign_keys
WHERE name = 'FK_Artists_Country';

Result:

Changed database context to 'WideWorldImportersDW'.
+--------------------+----------------------+--------------------------+
| Foreign Key        | Parent Object Name   | Referenced Object Name   |
|--------------------+----------------------+--------------------------|
| FK_Artists_Country | Artists              | Country                  |
+--------------------+----------------------+--------------------------+
(1 row affected)