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.
object_id() does not work for remote databases.
For objects that are not found in the sys.objects catalog view, obtain
the object identification numbers by querying the appropriate catalog
SQL that provides an answer to your query requirement is here.