Sql-server – How to fetch the columns of the synonyms for linked servers in SQL server

querysql serversynonyms

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?

Best Answer

Your error is that you ask your local server about the object from another, remote server.

OBJECT_ID(sys.synonyms.base_object_name) from your code will be resolved on a local server because OBJECT_ID() operates within local server, but you pass to it the name from remote server, so you'll get NULL here.

If you want to get remote object's object_id, you should do smth like this:

exec ('select object_id(''[remote_db].[remote_schema].[remote_table]'')') at [remote_server]

And in general, if you want to get the columns of remote object, you should execute your code on the remote server, looking for the columns of remote object in the remote sys.columns in the remote database, for example, this way:

declare @obj_name sysname = '[remote_server].[remote_db].[remote_table]';
declare @code nvarchar(4000) =
N'select c.name   AS COL_NAME,
       t.name     AS data_typename
from sys.columns c
     join sys.types t
        on c.system_type_id = t.system_type_id
where c.object_id = object_id(@obj_name)';

declare @param_def nvarchar(400) = N'@obj_name sysname';

execute [remote_server].[remote_db].dbo.sp_executesql @code, @param_def, @obj_name = @obj_name;

UPDATE 0

Scenario : I have server A which has two database( database1 and (database2 ). I have created synonym in the database1. CREATE SYNONYM [dbo].[synonym] FOR database2.[dbo].[objectname] How to write query commonly to get object_id for this scenario and remote object ?

As Scott Hodgin mentioned, you can use PARSENAME() to get rid of remote_server's part of your object like this:

declare @cmd varchar(8000) = (select 
       'select object_id(' + 
       quotename (
       PARSENAME(base_object_name,3)  + '.' + 
       PARSENAME(base_object_name,2)  + '.' + 
       PARSENAME(base_object_name,1), '''')+ ')'
from sys.synonyms )

exec (@cmd) at [server_A];

UPDATE 1

I want to know whether I can access the stored procedure of the remote server(Server B) from local server(Server A).

...

I cannot get the list of the stored procedure.

You can get the list of remote stored procedures using this code:

select *
from remote_server.remote_db.sys.objects
where type = 'p';
Related Question