Search code examples
sql-serversql-server-2008

How to find all column names of a synonym

I usually find all column names of a table in sql server with the following sql

SELECT Upper(column_name) FROM information_schema.columns 
WHERE table_name ='mutable'

However, now I want to find all the column names for a synonym. What is a way to do this?

Solution

  • IMHO you shouldn't use INFORMATION_SCHEMA for any of this. The following will work for a table or a view:

    SELECT UPPER(name)
  FROM sys.columns
  WHERE OBJECT_NAME([object_id]) = N'mutable';

    The following will work for a synonym (a requirement you changed later):

    SELECT UPPER(name) 
  FROM sys.columns AS c
  CROSS APPLY
  (
    SELECT name
    FROM sys.synonyms 
    WHERE name = N'mutable'
    AND OBJECT_ID([base_object_name]) = c.[object_id]
  ) AS x;

    You can also just say:

    DECLARE @obj INT;

SELECT @obj = OBJECT_ID([base_object_name]) 
  FROM sys.synonyms WHERE name = N'mutable';

SELECT UPPER(name) FROM sys.columns WHERE [object_id] = @obj;