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.

“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.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

One thought on “SQL SERVER – Building Three-Part Name from OBJECT_ID – Database Name, Schema Name, TableName

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s