How To Check if a SQL Synonym Points to an Existing Object

The application I currently work with is quite a beast. It encompasses distributed systems, classic ASP, ASP.NET, PERL scripts, C++ applications, and a massive SQL infrastructure composed of over 2,000 client databases spanning around 20 SQL servers. We also have a particular server and set of databases our applications use to handle some central data and look up where a particular client’s data should be read from and written to. Linked servers, and synonyms that point to objects on them, have been put in place to manage cross-server queries in a uniform fashion, and to make deployment of database changes to these databases, via an awesome little application called RoundhousE, just a little bit easier. The Problem While a colleague and I were working on simplifying our synonym creation process, by reducing the amount of scripts necessary to create synonyms in different environments (dev, qa, production, etc.), it became clear that we needed a simple way to test that our new solution did not create incorrect synonyms without the need to regression test our entire set of data dependent applications. That’s when I thought, “Hey, why don’t we just query the sys.sysobjects view and attempt to execute a query against our synonyms?”

The Process

After putting a SQL script together that did that, I realized that it wouldn’t be that easy. I hadn’t thought about the fact that not only did our synonyms point to tables, they also pointed to stored procedures. So, after asking if there was some sort of SQL command I could run on a table, view, or stored procedure in a Stack Overflow question, I was pointed to the sys.synonyms view. From there, I could parse the base_object_name field and build a dynamic query on the synonym’s [LinkedServer].[Database].sys.sysobjects view to see if the sql object (stored procedure, table, or view) exists on that server.

 The Solution

After a few hours of refactoring my script and tweaking the output, I came up with the SQL script below. Running this script before and after our new synonym creation scripts allowed us to compare the before and after validity of all the synonyms on a database. Not only did it help us with this testing, but it also identified all the old, broken, misspelled, etc. synonym definitions that have been lingering on these databases forever! Hopefully, my little SQL lesson here can help out someone else down the road. I would love to hear from you if it does!

SET NOCOUNT ON

DECLARE /* for looping through sys.synonyms */
 @currentSynonym VARCHAR(255), 

 /* for parsing out [ServerName].[DatabaseName].[SchemaName].[ObjectName]
 from sys.synonyms.base_object_name */
 @baseObjectName NVARCHAR(1035),
 @originalBaseObjectName NVARCHAR(1035),
 @lastDelimiterIndex INT,
 @lastToken NVARCHAR(255),
 @sServer NVARCHAR (255),
 @sDatabase NVARCHAR(255),
 @sSchema NVARCHAR(255),
 @sObject NVARCHAR(255), 

 /* for testing if synonym points to an existing object */
 @sql NVARCHAR(1035),
 @objectCount INT, 

 /* for output formatting */
 @newLine NVARCHAR(2),
 @tab NVARCHAR(4),
 @validSynonyms NVARCHAR(MAX),
 @invalidSynonyms NVARCHAR(MAX);

 SET @validSynonyms = '';
 SET @invalidSynonyms = '';
 SET @newLine = CHAR(13) + CHAR(10);
 SET @tab = ' ';

 /* Grab fist synonym */
 SELECT @currentSynonym = MIN(name)
 FROM sys.synonyms WITH (NOLOCK);

 /* Loop through this DB's sys.synonyms view */
 WHILE @currentSynonym IS NOT NULL
 BEGIN
 SET @sObject = NULL;
 SET @sSchema = NULL;
 SET @sDatabase = NULL;
 SET @sServer = NULL;

 /* Parse out [server].[database].[schema].[object]
 from sys.synonyms.base_object_name */
 SELECT @baseObjectName = RTRIM(base_object_name)
 FROM sys.synonyms WITH (NOLOCK)
 WHERE name = @currentSynonym; 

 SET @originalBaseObjectName = @baseObjectName;

 WHILE LEN(@baseObjectName) > 0
 BEGIN
 SET @lastToken = NULL;
 SET @lastDelimiterIndex = CHARINDEX('.', @baseObjectName, 1) + 1;

 /* Find the last token in @manipulated string,
 from Right-to-Left, as the database and/or server
 may not be in sys.synonyms.base_object_name. */
 WHILE (CHARINDEX('.', @baseObjectName, @lastDelimiterIndex) > 0)
 BEGIN
 SET @lastDelimiterIndex = CHARINDEX('.',
 @baseObjectName,
 @lastDelimiterIndex) + 1;
 END
 SET @lastToken = SUBSTRING(@baseObjectName,
 @lastDelimiterIndex - 1,
 LEN(@baseObjectName) - @lastDelimiterIndex + 3); 

 /* Kind of kludgy, but I put the $ character at the end of
 the string and @lastToken, so that if 2 of the values
 match (i.e. object and database, object and schema, whatever)
 only the last one is replaced. */
 SET @lastToken = @lastToken + '$';
 SET @baseObjectName = @baseObjectName + '$';
 SET @baseObjectName = REPLACE(@baseObjectName, @lastToken, '');

 SET @lastToken = REPLACE(@lastToken, '.', '');
 SET @lastToken = REPLACE(@lastToken, '[', '');
 SET @lastToken = REPLACE(@lastToken, ']', '');
 SET @lastToken = REPLACE(@lastToken, '$', ''); 

 IF @sObject IS NULL
 SET @sObject = @lastToken;
 ELSE IF @sSchema IS NULL
 SET @sSchema = @lastToken;
 ELSE IF @sDatabase IS NULL
 SET @sDatabase = @lastToken;
 ELSE IF @sServer IS NULL
 SET @sServer = @lastToken;
 END

 IF @sDatabase IS NULL
 SET @sDatabase = DB_NAME();
 IF @sServer IS NULL
 SET @sServer = @@SERVERNAME;

 /* End of token sys.synonyms.base_object_name parsing */ 

 /* Query for the existence of the object on the database the
 synonym's object should be on and append to @validSynonyms
 or @invalidSynonyms as appropriate. */
 BEGIN TRY
 SET @sql = N'SELECT @count = Count(1) '
 + N'FROM [' + @sServer + '].[' + @sDatabase + '].sys.sysobjects '
 + N'WITH (NOLOCK) WHERE [name] = ''' + @sObject + ''';';

 EXECUTE sp_executesql @sql,
 N'@count INT OUTPUT',
 @count = @objectCount OUTPUT;

 If @objectCount > 0
 BEGIN
 SET @validSynonyms = @validSynonyms
 + @tab + N'* ' + @currentSynonym + @newLine;
 END
 ELSE
 BEGIN
 SET @invalidSynonyms = @invalidSynonyms
 + @tab + N'* ' + @currentSynonym + ' =>' + @newLine
 + @tab + @tab + 'Points to '
 + @originalBaseObjectName + @newLine;
 END
 END TRY
 BEGIN CATCH
 SET @invalidSynonyms = @invalidSynonyms + @tab
 + N'* ' + @currentSynonym + ' =>' + @newLine
 + @tab + @tab + ERROR_MESSAGE() + @newLine;
 END CATCH

 /* Grab next synonym */
 SELECT @currentSynonym = MIN(name)
 FROM sys.synonyms WITH (NOLOCK)
 WHERE name > @currentSynonym;
 END
 /*End of sys.synonym Loop*/

 PRINT 'Invalid Synonyms:' + @newLine + @newLine;
 PRINT @invalidSynonyms;
 PRINT @newLine + 'Valid Synonyms:' + @newLine + @newline;
 PRINT @validSynonyms;

 SET NOCOUNT OFF

The script will generate a nice little report that looks like:

Invalid Synonyms:

    * snInvalid01 =>
        Invalid object name '[ServerName].[DatabaseName].[Schema].nonExistantTable'.
    * snEtc =>
        Invalid object name '[InvalidDatabase].sys.validTable'.

Valid Synonyms:

    * snValid01
    * snValidEtc