Is there a T-SQL command that checks whether the object to which a synonym refers exists and is valid?

Question

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

Answer 1

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.

Answer 2

That looks like an awfully long script for such a simple check; what's wrong with just:

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