Question: How to Build Three Part Name from Object_ID?
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:
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)
6 Comments. Leave new
This also works
select ‘[‘+DB_Name()+’].[‘+s.name+’].[‘+o.name+’]’ from sys.objects o
inner join sys.schemas s
on s.schema_id = o.schema_id
where object_ID = 1154103152
Great Point!
This one also
select DB_Name()+’.’+SCHEMA_NAME(schema_id)+’.’+name from sys.objects
where object_ID = 1154103152
Great