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

Question: How to Build Three Part Name from Object_ID?

How to Build Three Part Name from Object_ID? - Interview Question of the Week #134 flatnumbers-800x560

Answer: I have heard about this multiple times before and I have blogged about this over here as well.

Think about it, if you only have object id, how will you get the name of the object along with the schema name and the context of the database it is running this query for. Well, when I ask this question, many people just think it is not possible. I know it is not possible for everyone to know every syntax, but sometimes I am looking forward to see if the user is able to think creative or not.

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 Schema Name 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:

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

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.

Let me know if you ever have to use this feature before. I personally find this very interesting, but would like to know your thoughts as well.

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

, , ,
Previous Post
How is Oracle Temporary Table Different from SQL Server? – Interview Question of the Week #133
Next Post
How to Show Results of sp_spaceused in a Single Result? – Interview Question of the Week #135

Related Posts

6 Comments. Leave new

Leave a Reply

Menu