synonym in SQL server 2005
How can one get the information about the object type of synonym that is whether the synonym belongs to function, stored procedure, table or view using a query. Sys.synonyms table contains information about synonyms present in a particular database but it does not tell for which object the synonym is created?
Software/Hardware used:
Software/Hardware used:
ASKED:
September 25, 2009 11:13 AM
UPDATED:
September 25, 2009 6:56 PM
Answer Wiki:
You have to use the base_object_name column to figure it out.
This code should do the trick.
<pre>select *, object_name(object_id(substring(base_object_name, charindex('.', base_object_name)+1, len(base_object_name))))
from sys.synonyms</pre>
You can now use sys.objects to get the base object type.
Something like this:
<pre>SELECT o.name,o.type_desc
FROM sys.objects o JOIN sys.synonyms s
ON object_name(object_id(substring(s.base_object_name, charindex('.', s.base_object_name)+1, len(s.base_object_name)))) = o.name
WHERE s.name = '<b>your_synonym</b>'</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
