When using SQL Server, if you ever find yourself about to do a join against the sys.objects system catalog view in order to get the name of an object, maybe stop and read this first.

Transact-SQL has a built-in function called OBJECT_NAME() that returns the name of an object, based on its ID.

In other words, if you have the object’s ID (for example, the object_id column), you can simply pass that ID to the OBJECT_NAME() function, and it will return the object’s name for you – no join required!

Example 1 – Basic Usage

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

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

Result:

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

Here, we can see that the first two columns display the object’s name and ID respectively. The third column uses the OBJECT_NAME() function to return the name from the ID.

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

The next example shows where OBJECT_NAME() can come in handy.

Example 2 – A More Useful Example

In this example, I return information about a foreign key by querying the sys.foreign_keys system catalog view.

First, let’s select all columns to see what’s returned by this view:

USE Music;
SELECT * 
FROM sys.foreign_keys
WHERE name = 'FK_Artists_Country';

Result (using vertical output):

name                           | FK_Artists_Country
object_id                      | 1253579504
principal_id                   | NULL
schema_id                      | 1
parent_object_id               | 885578193
type                           | F 
type_desc                      | FOREIGN_KEY_CONSTRAINT
create_date                    | 2019-08-27 16:14:39.560
modify_date                    | 2019-08-28 03:28:07.040
is_ms_shipped                  | 0
is_published                   | 0
is_schema_published            | 0
referenced_object_id           | 1205579333
key_index_id                   | 1
is_disabled                    | 0
is_not_for_replication         | 0
is_not_trusted                 | 0
delete_referential_action      | 0
delete_referential_action_desc | NO_ACTION
update_referential_action      | 0
update_referential_action_desc | NO_ACTION
is_system_named                | 0

This view returns the name of the foreign key, but not the name of its parent object. Nor does it return the name of the foreign key’s referenced object. It only returns the ID of those objects (namely, parent_object_id and referenced_object_id).

So if we were to narrow it down to just those columns, we’d get something like this:

USE Music;
SELECT
  name,
  parent_object_id,
  referenced_object_id
FROM sys.foreign_keys
WHERE name = 'FK_Artists_Country';

Result:

+--------------------+--------------------+------------------------+
| name               | parent_object_id   | referenced_object_id   |
|--------------------+--------------------+------------------------|
| FK_Artists_Country | 885578193          | 1205579333             |
+--------------------+--------------------+------------------------+

Fortunately, we can pass the last two columns to OBJECT_NAME() in order to retrieve the object names.

Here’s what we can do to return the names.

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 sys.foreign_keys
WHERE name = 'FK_Artists_Country';

Result:

+--------------------+----------------------+--------------------------+
| Foreign Key        | Parent Object Name   | Referenced Object Name   |
|--------------------+----------------------+--------------------------|
| FK_Artists_Country | Artists              | Country                  |
+--------------------+----------------------+--------------------------+

For the last two columns, I pass the relevant values to the OBJECT_NAME() function so that it returns the name of each parent object.

Example 3 – Using OBJECT_NAME() in a WHERE Clause

Here’s an example of using OBJECT_NAME() in a WHERE clause.

SELECT 
  name, 
  object_id, 
  type_desc  
FROM sys.objects  
WHERE name = OBJECT_NAME(1253579504);

Result:

+--------------------+-------------+------------------------+
| name               | object_id   | type_desc              |
|--------------------+-------------+------------------------|
| FK_Artists_Country | 1253579504  | FOREIGN_KEY_CONSTRAINT |
+--------------------+-------------+------------------------+

Cross Database Queries

By default, SQL Server assumes that the object ID is in the context of the current database. A query that references an ID in another database returns NULL or incorrect results.

If you need to find an object name from a different database, you can provide that database’s ID as a second argument when calling OBJECT_NAME().

See How to Get an OBJECT_NAME() from a Different Database in SQL Server for examples.