How to get column metadata from a table synonym

Question

How can I determine column metadata from a table synonym in a SQL Server 2005 database? I have a synonym called 'ProjectSyn' for a table called 'Project', but I can find no column metadata for the synonym.

My guess is to somewhere determine the 'base table' for the synonym, then query for column metadata for that table. Is this a correct approach, and if not, what would be?

@Aliostad - An alternate name for a database object. Useful for renaming objects without breaking legacy code (at least in theory - Sometimes the abstraction doesn't work as in this question). Syntax: `CREATE SYNONYM ProjectSyn FOR Project` — Martin Smith, Dec 07 '10 at 13:00
@Aliostad, I have added a link on the term synonym for an MSDN introduction. — ProfK, Dec 07 '10 at 13:41

score 4 · Answer 1 · edited May 20 '15 at 23:10

4

This is my solution which works with synonyms of different databases:

SELECT TOP 0 * INTO #TEMP1 FROM YourTable 
SELECT
    [column_name] = c.name,
    [data_type] = t.name,
    [character_maximum_length] = c.max_length
FROM tempdb.sys.columns c
inner join tempdb.sys.types t on t.system_type_id = c.system_type_id
WHERE [object_id] = object_id('tempdb..#TEMP1');
DROP TABLE #TEMP1

edited May 20 '15 at 23:10

Arkaine55

548
6
15

answered Nov 25 '14 at 12:20

apc

5,306
1
17
26

What about column meta data? That is not included in this answer. – Taersious Mar 16 '15 at 19:12
tempdb.sys.columns c or tempdb.sys.types t contain the meta data fields you will need for example t.name will provide the type name. Try the script with SELECT * to see the availiable fields. – apc May 21 '15 at 13:45
this is simple and give us something to build on. Would be clearer if the 1st line read: SELECT TOP 0 * INTO #TEMP1 FROM *YourSynonym* – Trubs Apr 07 '21 at 00:56

score 1 · Answer 2 · answered Dec 07 '10 at 12:52

1

Yes, I think getting the base object and then retrieve the columns, is your only option.

To get the base object name for a synonym, just query the view sys.synonyms

answered Dec 07 '10 at 12:52

score 1 · Accepted Answer · edited Apr 26 '11 at 19:52

1

Something like this? (edited)

select c.*
from
   sys.columns c
   inner join sys.synonyms s on c.object_id = object_id(s.base_object_name)
where
   s.name = 'ProjectSyn'

edited Apr 26 '11 at 19:52

ErikE

48,881
23
151
196

answered Dec 07 '10 at 12:58

Jonas Lincoln

9,567
9
35
49

Thanks @Jonas, but your code needs a little work. The OBJECT_NAME function gives an unqualified name, where base_object_name is fully qualified. – ProfK Dec 07 '10 at 13:31
2

Of course, this will return nothing if the synonym is for an object in a different database on the same server, or even an object on a linked server. Also note that if the synonym was created with brackets as in `[ProjectSyn]` then the base_object_name column will need to have those included as well. – ErikE Apr 26 '11 at 19:53
@ErikE The answer by apc works across databases and even linked servers without needing to match brackets. – Arkaine55 May 20 '15 at 21:04
@Arkaine55 I don't understand why you're telling me about a different answer than the one I commented on. Your words are a complete non sequitur. If you want to comment on the other answer that it works across databases & linked servers, go ahead. However, I will say that the only way to work across the linked server is to query the linked server. A query against a local `object_id` is NOT going to work against a linked server. – ErikE May 21 '15 at 19:47

How to get column metadata from a table synonym

3 Answers3