0

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.

Colin 't Hart
  • 9,455
  • 15
  • 36
  • 44
Subaashini Pushparaju
  • 53
  • 1
  • 5

1 Answers1

2

object_id() does not work for remote databases.

The documentation states:

For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view

SQL that provides an answer to your query requirement is here.

Philᵀᴹ
  • 31,952
  • 10
  • 86
  • 108