I have used two servers, Server A(local server) and Server B(remote server). I have created synonyms in the Server A for the Server B table. I have created synonym using following syntax.
CREATE SYNONYM RemoteServerATable FOR Server B .[database_name].[schema_name].object_name
I have tried to join two tables sys.columns and sys.synonyms using below query
select s.name as [SynonymName], c.name as [ColumnName], c.object_id from [Server B].[DatabaseName].sys.columns c inner join sys.synonyms s on s.name = 'RemoteServerATable' and c.object_id = 1973582069
I have joined two tables using object_id of the sys.columns tables in the other server and hard coded integer value. I got this value using below query
exec ('select object_id(''[Server B DatabaseName].[Server B SchemaName].[Server B ObjectName]'') as object_id') at [Server B]
I am not getting correct columns information. Kindly suggest someways join two tables and get the column information.