SQL SERVER – Building Three-Part Name from OBJECT-ID – Database Name, Schema Name, TableName

Here is the question I recently received based on my earlier blog post – SQL SERVER – Get Schema Name from Object ID using OBJECT_SCHEMA_NAME. The reader wanted to build a three-part name from the object_id alone.

“How I get three part name if the only thing I have is object_id and current database context. What I mean is that, when I query executes, I do not know for which database it is executing as the name of the database is part of the connection string. I only know the name of the object_id. I have already read your earlier article but I need further guidance. Can you help?”

Here is the script which will generate three part name based out of just object_id. For example we have object_id 1154103152 which belongs to AdventureWorks database, Sales SchemaName and SalesOrderDetails table. Here is how you can generate three part namings.

SELECT QUOTENAME(DB_NAME())

+N'.'+QUOTENAME(OBJECT_SCHEMA_NAME(1154103152))

+N'.'+QUOTENAME(OBJECT_NAME(1154103152));

The above query will give us following result:

Instead of passing static variables you can also pass the column containing object_id into the function and get three part name of the objects.

Why a Three-Part Name Needs the Right Database

An object_id is unique only inside one database. The same number can point to a different table, or to nothing at all, in another database. That is why the script works only in the current database context.

When your object_id comes from somewhere else, for example a DMV that also returns a database_id, pass that database_id as the second argument: OBJECT_SCHEMA_NAME(object_id, database_id) and OBJECT_NAME(object_id, database_id). Combine them with DB_NAME(database_id) and you get the full name for any database on the instance without switching context. This is handy in logging and auditing code, where you save the object_id and database_id first and turn them into readable names later.

Also watch for NULL. These functions return NULL when the object does not exist or when you do not have permission to see it, and one NULL in the string concatenation turns the whole three-part name into NULL. Wrap each piece in ISNULL while you test, so you can see which part is missing.

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