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

是否有一个命令可以在sproc,table或view上执行,如果它不存在会抛出错误？

同义词可以指向链接服务器上的对象,也可以指向当前@@ SERVERNAME上的对象,因此我不能真正只查询catch块中的sys.procedures以查看它是否存在.我必须知道同义词是否指向链接服务器,然后我必须查询该服务器的sys.procedures视图.

我发现了一个类似的问题,How can I check if the table behind a synonym exists,但它并不是我所要求的.我发现的其他所有内容都是Oracle特有的.

更新：下面的脚本适合我的需要.感谢@ kenny-evitt获取我需要的信息.

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('.',@lastDelimiterIndex) > 0)
        BEGIN
            SET @lastDelimiterIndex = CHARINDEX('.',@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,'[',']','$','');              
 
        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