I’m trying to test if all synonyms on a database refer to valid objects, using the SQL batch script below, as part of a clean-up effort on our databases. This script just performs a query against the synonym, and printing out any errors it encounters. This works fine 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’s an command that I can perform on a sproc, table, or view that will throw an error if it doesn’t exist?
The synonyms can either point to an object on a linked server or an object on the current @@SERVERNAME, so I can’t really just query sys.procedures in the catch block to see if it’s there. I’d have to know if the synonym points to a linked server, and then I’d have to query that server’s sys.procedures view.
I found a similar question, How can I check if the table behind a synonym exists, but it isn’t quite what I’m asking. Everything else I found was Oracle specific.
UPDATE: The script below works for my needs. Thank’s to @kenny-evitt for the information I needed to form 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's 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 (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. */
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
Advertisement
Answer
You can use OBJECT_ID with the base_object_name column of sys.synonyms to test whether the base objects exist:
SELECT [Schema] = sch.name,
[Name] = syn.name,
syn.base_object_name,
[Base object exists?] = CASE WHEN OBJECT_ID(syn.base_object_name) IS NOT NULL THEN 'Yes' ELSE 'No' END
FROM sys.synonyms syn
JOIN sys.schemas AS sch ON syn.schema_id = sch.schema_id
ORDER BY [Base object exists?], [Schema], [Name];
The query was adapted from this answer to this same question.