I am using two servers local (SQL Server running on my system) and remote (SQL Server running on another system). I have created synonyms in my local server for a table which is located on the remote server.
Synonym used- CREATE SYNONYM [dbo].[test] FOR remoteserver_name .[database_name].[schema_name].object_name
Requirement
I need to fetch the columns of the synonym.
I can retrieve the columns of the synonym using below query in SQL server.
select * from dbo.test
where
dbo
is synonym created schema name (local server schema name) and test
is synonym name.
I have tried to retrieve the columns of the synonyms using below query, but I cannot fetch the columns
SELECT sys.schemas.name AS SCHEMA_NAME,
sys.synonyms.name AS view_name,
sys.columns.name AS COL_NAME,
sys.types.name AS data_typename
FROM sys.columns
INNER JOIN sys.synonyms
ON OBJECT_ID(sys.synonyms.base_object_name) = sys.columns.object_id
INNER JOIN sys.schemas
ON sys.schemas.schema_id = sys.synonyms.schema_id
LEFT OUTER JOIN sys.types
ON sys.columns.system_type_id = sys.types.system_type_id
WHERE sys.types.system_type_id = sys.types.user_type_id
AND sys.schemas.name = N'scehmaname'
AND sys.synonyms.name = N'synonymname'
Can you please suggest some other way to retrieve the columns of the synonym or correct the above query?
sys.xyz
tables, do you mean you would like to retrieve the defintions of your synonyms? Or just an overview of your synonyms? Thank you for putting in some additional effort into your question.