title: Is there a T-SQL command that checks whether the object to which a synonym refers exists and is valid?
body: <p>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. </p>
  
  <pre><code>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 &gt; @currentId;
  END
  
  SET NOCOUNT OFF
  </code></pre>
  
  <p><strong>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?</strong></p>
  
  <p>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.</p>
  
  <p>I found a similar question, <a href="https://stackoverflow.com/q/15639907/178207">How can I check if the table behind a synonym exists</a>, but it isn't quite what I'm asking. Everything else I found was Oracle specific.</p>
  
  <hr>
  
  <p><strong>UPDATE:</strong> The script below works for my needs. Thank's to @kenny-evitt for the information I needed to form it.</p>
  
  <pre><code>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) &gt; 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) &gt; 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 &gt; 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 + ' =&gt;' + @newLine;
          SET @invalidSynonyms = @invalidSynonyms + @tab + @tab + ERROR_MESSAGE() + @newLine;
      END CATCH
  
  
      SELECT @currentSynonym = MIN(name) FROM sys.synonyms WITH (NOLOCK) WHERE name &gt; @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
  </code></pre>