Need to get object type for synonym base object (2012)

Question

Need to get object type for synonym base object (2012)

jmartincano

SSC-Addicted

Points: 407
More actions
June 17, 2014 at 1:45 am

#287655

Hi all,
I need to get the object type (view, table ...etc) for a synonym base object inside a script. The only place where I see something related to this stored is in column "base_object_name" in sys.synonyms but there I can see only the same with format [database]..[name]. After some testing playing with different users without specifying database/schema I think that maybe the object_id must be stored in a another place, my first idea was parent_object_id in sys.synonyms but it isn't stored there.
Does anyone know if object_id for the base object is stored in any other place ?, any idea to get this ?
Thanks in advance.
Javier.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

jmartincano (6/17/2014)
Hi all,
I need to get the object type (view, table ...etc) for a synonym base object inside a script. The only place where I see something related to this stored is in column "base_object_name" in sys.synonyms but there I can see only the same with format [database]..[name]. After some testing playing with different users without specifying database/schema I think that maybe the object_id must be stored in a another place, my first idea was parent_object_id in sys.synonyms but it isn't stored there.
Does anyone know if object_id for the base object is stored in any other place ?, any idea to get this ?
Thanks in advance.
Javier.

You can easily retrieve the OBJECT_ID from there.

select OBJECT_ID(base_object_name)

from sys.synonyms s

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Look at this: http://msdn.microsoft.com/en-us/library/ms176105.aspx

As long as you are on the same server it should work. Here is some sample code to start with:

select

syn.name SynonymName,

syn.base_object_name,

objectproperty(object_id(syn.base_object_name),'IsTable'),

objectproperty(object_id(base_object_name),'IsView')

from

sys.synonyms syn;

jmartincano SSC-Addicted Points: 407 More actions · Answer 3

The problem is that I am not able to see the object id when the synonym has been created with a different user than the one running the query, for example, I have 3 synonyms, one created with sa, one created with user j_login1 and last one created with user j_login2. If I run the query with sa:

C:\Users\Administrator\Desktop>sqlcmd -Usa -P<passwd> -S<server>

1> use testdb

2> go

Changed database context to 'testdb'.

1> select name, object_id (base_object_name) from sys.synonyms

2> go

name

-------------------------------------------------------------------------------------------------------------------------------- -----------

j_test 388196433

j_login2_syn NULL

j_login1_syn NULL

(3 rows affected)

1>

But if I run it with j_login1:

C:\Users\Administrator\Desktop>sqlcmd -Uj_login1 -P<passwd> -S<server>

1> use testdb

2> go

Changed database context to 'testdb'.

1> select name, object_id (base_object_name) from sys.synonyms

2> go

name

-------------------------------------------------------------------------------------------------------------------------------- -----------

j_login1_syn 1220199397

(1 rows affected)

1>

Any other ideas ?. As far as I expected with sa I should see all.

Thanks a lot in advance.

Lowell SSC Guru Points: 323518 More actions · Answer 4

what happens when you include the shcme aname explicitly?

select schema_name(schema_id),name, object_id (base_object_name),object_id (schema_name(schema_id) + '.' + base_object_name) from sys.synonyms

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Lowell (6/17/2014)
what happens when you include the shcme aname explicitly?
select schema_name(schema_id),name, object_id (base_object_name),object_id (schema_name(schema_id) + '.' + base_object_name) from sys.synonyms

The column base_object_name contains the <database>.<schema_name>.<object_name> of the synonym base object.

jmartincano SSC-Addicted Points: 407 More actions · Answer 6

Adding the schema seems to work properly at least in my basic tests.

Cheers and thanks a lot.