Is there a T-SQL command that checks if an object that the synonym belongs to is valid?

Question

I am trying to check if all synonyms in the database match valid objects using the SQL script package below, as part of the effort to clean up our databases. This script simply executes a synonym request and prints out any errors it encounters. This works great on views and tables, but not on sprocs.

SET NOCOUNT ON DECLARE @currentId INT; DECLARE @currentSynonym VARCHAR(255); SELECT @currentId = MIN(id) FROM sysobjects WITH (NOLOCK) WHERE [xtype]='SN'; WHILE @currentId IS NOT NULL BEGIN SELECT TOP(1) @currentSynonym = name FROM sysobjects WITH (NOLOCK) WHERE id = @currentId; PRINT ''; PRINT '------------------------------------------------------------'; PRINT @currentSynonym; PRINT '------------------------------------------------------------'; BEGIN TRY EXEC('SELECT Top(1) NULL FROM ' + @currentSynonym + ' WITH (NOLOCK);'); PRINT 'Synonym is valid.'; END TRY BEGIN CATCH PRINT ERROR_MESSAGE(); END CATCH SELECT @currentId = MIN(id) FROM sysobjects WITH (NOLOCK) WHERE [xtype]='SN' AND id > @currentId; END SET NOCOUNT OFF 

Is there a command that I can execute in sproc, table or view that throws an error if it does not exist?

Synonyms can point to an object on a linked server or an object in the current @@ SERVERNAME, so I can’t just query sys.procedures in a catch block to see if it is there. I would have to know if a synonym points to a linked server, and then I will have to request a representation of the sys.procedures server.

I found a similar question, How to check if a synonym table exists , but this is not quite what I am asking for. Everything else I found was specific to Oracle.

---

UPDATE: the script below works for my needs. Thanks @ kenny-evitt for the information I need to create it.

 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), @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 = ' '; /* Loop through this DB sys.synonyms view */ SELECT @currentSynonym = MIN(name) FROM sys.synonyms WITH (NOLOCK); 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; WHILE LEN(@baseObjectName) > 0 BEGIN SET @lastToken = NULL; SET @lastDelimiterIndex = CHARINDEX('.', @baseObjectName, 1) + 1; -- Find the last token in @manipulated string, -- Do this 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 (ie 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 object should be on. */ BEGIN TRY SET @sql = N'SELECT @count = Count(1) FROM [' + @sServer + '].[' + @sDatabase + '].sys.sysobjects WITH (NOLOCK) WHERE [name] = ''' + @sObject + ''';'; EXECUTE sp_executesql @sql, N'@count INT OUTPUT', @count = @objectCount OUTPUT; If @objectCount > 0 SET @validSynonyms = @validSynonyms + @tab + N'* ' + @currentSynonym + @newLine; ELSE SET @invalidSynonyms = @invalidSynonyms + @tab + N'* ' + @currentSynonym + @newLine; END TRY BEGIN CATCH SET @invalidSynonyms = @invalidSynonyms + @tab + N'* ' + @currentSynonym + ' =>' + @newLine; SET @invalidSynonyms = @invalidSynonyms + @tab + @tab + ERROR_MESSAGE() + @newLine; END CATCH 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 

Answer 1

There is no built-in command / function / etc that you could write. I would suggest using a language other than T-SQL, but this is certainly only possible in it.

You will need to base_object_name values ​​in the base_object_name column in the base_object_name system catalog sys.synonyms .

Answer 2

It looks like a terribly long script for such a simple check; what's wrong:

 if exists (select * from sys.synonyms where name = @currentSynonym and object_id(base_object_name) is not null) begin --Add logic here end