How to Build Three Part Name from Object_ID? – Interview Question of the Week #134

Interview question: Can you build a three-part SQL Server name from an object_id?

Answer: Yes, if you also know the database context. Within that database, combine DB_NAME, OBJECT_SCHEMA_NAME, and OBJECT_NAME, wrapping each part with QUOTENAME. An object ID is unique within a database, not across the whole instance, so an isolated number does not tell you which database to search.

I have asked this question more than once in interviews. I am not expecting everyone to memorize every metadata function; I want to see whether the candidate asks, “Which database does that ID belong to?” I explored a related example in my earlier three-part-name article.

Run the following in the database that contains Sales.SalesOrderDetail, such as a suitable AdventureWorks sample. Getting the ID from the name makes the example portable across database versions:

USE AdventureWorks2025;
GO
DECLARE @ObjectId int = OBJECT_ID(N'Sales.SalesOrderDetail');

SELECT CASE WHEN @ObjectId IS NULL THEN NULL
            ELSE QUOTENAME(DB_NAME()) + N'.'
               + QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectId)) + N'.'
               + QUOTENAME(OBJECT_NAME(@ObjectId))
       END AS three_part_name;

The expected shape is [AdventureWorks...].[Sales].[SalesOrderDetail]; the database part reflects the actual sample you use. QUOTENAME safely delimits names containing spaces or punctuation.

My original demonstration hard-coded object ID 1154103152 in an AdventureWorks2012 database. The historical SSMS capture below shows that exact database-specific result:

SELECT QUOTENAME(DB_NAME()) + N'.'
     + QUOTENAME(OBJECT_SCHEMA_NAME(1154103152)) + N'.'
     + QUOTENAME(OBJECT_NAME(1154103152));

Do not paste that numeric ID into a different database and expect the same name. If your application knows the database ID separately, pass it as the optional second argument to OBJECT_NAME and OBJECT_SCHEMA_NAME, and use DB_NAME for the same database ID. A missing object or insufficient metadata visibility can return NULL; handle that instead of concatenating an incomplete name.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.