是否有T-SQL命令检查同义词所引用的对象是否存在且有效？

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

解决方法

您需要解析系统目录视图sys.synonyms中base_object_name列中的值.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!