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?”
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.
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