THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Script to list synonym contents

Synonyms are useful in repointing code to point to a different location without changing the code. Some shops use synonyms to deploy the same T-SQL source code to different servers (i.e., development, test, production). I was asked to provide a simple report of what all of the synonyms are actually pointing to. Seems simple, right?

 

select name, base_object_name
from sys.
synonyms
order by name

 

The problem is that the results aren't convenient for people to read if different databases and schemas are used. SQL Server doesn't have a splitter function, so some type of string manipulation is called for. There are good T-SQL splitter functions that can be found with a search engine, but it's more convenient for people to have a complete, standalone solution with no dependencies.

Writing custom splitting logic takes care. You don't want a false positive messing up the splitting logic. In deference to test driven development, here are my test cases:

 

create table [test.me] (a int);
create synonym testme for [test.me]
;
create synonym schemaTestme for dbo.[test.me]
;
create synonym dbSchemaTestme for sqlblogdb.dbo.[test.me];
create synonym svrDbSchemaTestme for SQLBLOGSVR.sqlblogdb.dbo.[test.me];

After I posted custom splitter logic that worked, Denis Gobo pointed out that the PARSENAME function eliminates the need for any custom splitting logic and it keeps the code simple. Dennis, thanks for the tip. Other readers wanted four part synonyms to be covered, so I modified the code and test cases accordingly. The COALESCE function is necessary to prevent NULL from appearing in the output when the synonym has less than four parts. For example, without COALESCE, the testme synonym would have NULL for serverName, dbName, and schemaName.

 

SELECT

name,
COALESCE(PARSENAME(base_object_name,4),@@servername) AS serverName,
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName,
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
PARSENAME(base_object_name,1) AS objectName
FROM sys.synonyms
ORDER BY serverName,dbName,schemaName,objectName


name              serverName dbName    schemaName objectName
----------------- ---------- --------- ---------- ----------
testme            SQLBLOGSVR thisdb    thisschema test.me
schemaTestme      SQLBLOGSVR thisdb    dbo        test.me
dbSchemaTestme    SQLBLOGSVR sqlblogdb dbo        test.me
svrDbSchemaTestme SQLBLOGSVR sqlblogdb dbo        test.me
Published Tuesday, August 24, 2010 2:18 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Emtucifor said:

For what it's worth, synonyms can use four-part naming, so if the synonym points to something on a linked server, your query won't show that.

And in case you're wondering, yes, I do have 5 synonyms in one of my databases that point to a linked server and get used (some of them every 10 minutes 24 hours a day).

August 24, 2010 6:19 PM
 

ALZDBA said:

Emtucifor : This flaw can be corrected by adding

     , COALESCE(PARSENAME(base_object_name, 4), @@servername) AS ServerName

August 25, 2010 1:46 AM
 

Emtucifor said:

Of course it can, ALZDBA! It's nice to see the code complete, though.

August 25, 2010 4:03 PM
 

Bob said:

So Sweet!  Exactly the remedy to all my anguish

August 14, 2012 10:22 PM
 

stewartwb said:

There is one potential issue I see.  Your results show the absolute location of the object to which that the synonym points.  However, a synonym can be declared in either an absolute or a relative way.  When a synonym's object is referenced only by [schema].[table], it resolves to [current server].[current db].[schema].[table] for whatever server and database is in scope.  Developers who aren't careful of the difference can introduce subtle bugs that are hard to correct.  I suggest the following SQL script to include the actual definition of the referenced object (relative or absolute), along with the four columns you included to show the object to which the synonym resolves.

SELECT

name as synonymName,

base_object_name as synonymDefinition,

COALESCE(PARSENAME(base_object_name,4),@@servername) AS serverName,

COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName,

COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,

PARSENAME(base_object_name,1) AS objectName

FROM sys.synonyms

ORDER BY serverName,dbName,schemaName,objectName

September 19, 2012 11:24 AM
 

Beverley said:

Is there a way to do something like this, but run it across all databases on a server?  

August 1, 2013 5:40 PM
 

Beverley said:

I figured out I could do this -- not sure if it is GOOD, but it works.

select

'UNION SELECT ''' + name + ''' as OwningDatabase,name, COALESCE(PARSENAME(base_object_name,4),@@servername) AS serverName, COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName, COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName, PARSENAME(base_object_name,1) AS objectName FROM ' + name + '.sys.synonyms '

from sys.databases where name <> 'msdb' and state = 0 -- online

Run the query, copy & paste the results and run that (deleting the extra UNION off the first line)

August 1, 2013 6:00 PM
 

Meher said:

awesome dude thanks

September 18, 2013 7:29 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement