4

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?

  • I am having a hard time understanding what you are trying to achieve. Could you give us an example how you created a synonym on your server? (Edit your question and add the details) Then when you state that your are trying to get the same result by querying the sys.xyz tables, do you mean you would like to retrieve the defintions of your synonyms? Or just an overview of your synonyms? Thank you for putting in some additional effort into your question. – John aka hot2use Jul 18 '18 at 6:25
  • 1
    I have edited my question, can you please suggest solution for my problem? – Subaashini Pushparaju Jul 18 '18 at 12:19
4

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';
  • Thanks for your solution, its working fine. May I know how to get object_Id for below mentioned scenario 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 ? Kindly suggest me query. – Subaashini Pushparaju Jul 19 '18 at 6:43
  • @Subaashini Pushparaju I updated my answer with the new code for object_id() – sepupic Jul 19 '18 at 7:03
  • Thanks. I have one more requirement in the synonym. Kindly suggest some ways or query to solve this. I need solution for two scenarios. Consider Server A(local server) and Server B(remote server). Scenario 1 - I have created synonym in the server A for stored procedure in the server B. Scenario 2 - I have create synonym in the server A for stored procedure in the server A. I cannot retrieve the columns of the synonym stored procedure. – Subaashini Pushparaju Jul 20 '18 at 7:06
  • @Subaashini Pushparaju, sorry, what do you mean saying "columns of the synonym stored procedure", stored procedures do not have columns, do you mean something else? – sepupic Jul 20 '18 at 7:35
  • Sorry, I have updated my requirement wrongly. I want to know whether I can access the stored procedure of the remote server(Server B) from local server(Server A). I have linked two servers, Server A and Server B. I tried to access the stored procedure of the server B in the Server A. I cannot access it so can please conform whether I can access the stored procedure. – Subaashini Pushparaju Jul 20 '18 at 8:46
3

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.