Checking to see if a synonym is used

Question

Checking to see if a synonym is used

Ray K

SSC-Dedicated

Points: 31166
More actions
November 16, 2012 at 9:09 am

#265982

Hi all . . .
A while back, I created a synonym that I ended up not really using. However, I'm not sure about any dependencies on it.
I'd like to use the same name for a new table I want to build. But before I do so, I want to make sure that using DROP SYNONYM isn't going to break something else (stored procedures, etc.).
Anyone know if there's a good way to do this?
Thanks!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i *think* this would give you a list of dependancy items that reference an existing synonym in a specific database.

note you can have global synonyms/ cross database synonyms in the master database/other databases as well, so check there too.

edited to fix with tested code.

SELECT

object_name(depz.referencing_id) As ReferencingObject ,

depz.referenced_schema_name,

depz.referenced_entity_name,

objz.type_desc

FROM sys.sql_expression_dependencies depz

INNER JOIN sys.objects objz ON depz.referenced_id=objz.object_id

WHERE objz.type_desc = 'SYNONYM'

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!

Ray K SSC-Dedicated Points: 31166 More actions · Answer 2

Well, I ran the query, and it came up empty. So I guess that means there are no dependencies. Either that, or it's not working, and I don't know it! 😉

Thanks, as always!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Steve Jones - SSC Editor SSC Guru Points: 736534 More actions · Answer 3

I'm not positive that will show all the dependencies. If you have SQL Compare[/url] (from Red GAte) or a similar tool, they usually do a better job of searching out dependencies.

You could also use SQL Search[/url] (Free) to look for the name of the object.

Disclosure: I work for Red Gate

Ray K SSC-Dedicated Points: 31166 More actions · Answer 4

Ray K (11/16/2012)
Well, I ran the query, and it came up empty. So I guess that means there are no dependencies. Either that, or it's not working, and I don't know it! 😉
Thanks, as always!

Okay, slight modification: I changed 'SYNONYM' to [synonym name], where I should've kept it as is. (Oops.) I changed it back, and it returned one entry. However, the synonym object it returned was not the one I was going to use. So, I guess it'd be okay.

FWIW, I already used DROP SYNONYM on the object in question in my development environment, and I haven't seen anything bomb (yet).

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Lowell SSC Guru Points: 323518 More actions · Answer 5

here was my test...i made a cross database synonym in one database,a dn then a procedure in the same database as the synonym:

create synonym MymasterView for SandBox.dbo.VW_FIXEDWIDTH

create procedure pr

as select * from MymasterView

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!