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

Question

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?

Answer 1

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';

Answer 2

I upvoted sepupic's answer.

This is another example of how you could do this - it may need to be adjusted per your requirements.

I'm using dynamic SQL and sp_columns.

In my example, I created a synonym called syntest as follows:

CREATE SYNONYM [dbo].[SynTest] FOR [SqlCompare].[SCOPERATIONAL_DBCC].[SCFBDM].[CFG_LINE_BUS]

This synonym points to server SqlCompare, database SCOPERATIONAL_DBCC, schema SCFBDM and table CFG_LINE_BUS.

Issuing select * from sys.synonyms where name = 'syntest' reveals a base_object_name of [SqlCompare].[SCOPERATIONAL_DBCC].[SCFBDM].[CFG_LINE_BUS]. I can use PARSENAME to extract the individual parts of the base_object_name to build the dynamic SQL statement

Declare @Cmd nvarchar(4000)

select @Cmd = 
    'exec ' + PARSENAME(base_object_name,4) + '.' + 
    PARSENAME(base_object_name,3) + 
    '.dbo.sp_columns @table_name = ' + '''' + 
    PARSENAME(base_object_name,1) + 
    ''',@table_owner = ' + 
    '''' +  PARSENAME(base_object_name,2) + ''''
    from sys.synonyms where name = 'syntest'    --<<<< The name of your synonym
PRINT @CMD
exec sp_executesql @cmd

The PRINT statement shows the built dynamic SQL as

exec SqlCompare.SCOPERATIONAL_DBCC.dbo.sp_columns @table_name = 'CFG_LINE_BUS',@table_owner = 'SCFBDM'

Answer 3

You can follow How to find all column names of a synonym - Stack Overflow:

SELECT TOP(0) * INTO #tmp_synonym_xyz FROM synonym_xyz;
SELECT name FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb.dbo.#tmp_synonym_xyz');

---

1. The trick is to take the top 0 (or where 1=0) and put a chosen table or all tabels that you need from sys into temp tables.

For a bunch of synonyms, you can write a sql or a dynamic sql script or take another programming language to make a list of the needed synonym names so that you just run them before you run your meta data query on the temporary tables. Begin perhaps with this, taken from How can I check if the table behind a synonym exists - Stack Overflow:

select 
    DB_NAME() AS SynonymsDatabaseName,
    SCHEMA_NAME(s.schema_id) AS SynonymsSchemaName,
    s.name AS SynonymsName,
    PARSENAME(s.base_object_name,3) AS DatabaseName,
    PARSENAME(s.base_object_name,2) AS SchemaName,
    PARSENAME(s.base_object_name,1) AS ObjectName
from sys.synonyms s
where s.name like '%xyz%'

Script a bunch of such select into #tmp_synonym_xyz commands and run them in a procedure or by hand. I do not take the time now for this, but it can be done so that all of the needed synonyms will be 0-rows-tables on tempdb.

2. And the SELECT name FROM tempdb.sys.columns ... can then fetch the list of the table column pairs.

---

With 1.) and 2.), you can get the synonyms and their column names even for another server.

Since for me, this seemed too much work for a small task that happens once in a while, that is why I also do not have the code ready. Instead, I will change to the synonym source server and run the script that joins the synonyms with the columns:

select 
    DB_NAME() AS SynonymsDatabaseName,
    SCHEMA_NAME(s.schema_id) AS SynonymsSchemaName,
    s.name AS SynonymsName,
    PARSENAME(s.base_object_name,3) AS DatabaseName,
    PARSENAME(s.base_object_name,2) AS SchemaName,
    PARSENAME(s.base_object_name,1) AS ObjectName,
    c.name
from sys.columns c
left join sys.synonyms s
on c.object_id = object_id(s.base_object_name)
where s.name like '%something%'
or c.name like '%anything%'

Thanks goes to the shared links.