sql - 是否有一个 T-SQL 命令可以检查同义词引用的对象是否存在且有效？

Question

我正在尝试使用下面的 SQL 批处理脚本来测试数据库上的所有同义词是否都引用有效对象，作为数据库清理工作的一部分。该脚本仅对同义词执行查询，并打印出遇到的任何错误。这适用于 View 和表，但不适用于存储过程。

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

是否有一个我可以在存储过程、表或 View 上执行的命令，如果该命令不存在，则会抛出错误？

同义词可以指向链接服务器上的对象，也可以指向当前 @@SERVERNAME 上的对象，因此我实际上不能只在 catch block 中查询 sys.procedures 来查看它是否存在。我必须知道同义词是否指向链接服务器，然后我必须查询该服务器的 sys.procedures View 。

我发现了类似的问题，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('.', @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

对于如此简单的检查来说，这看起来是一个非常长的脚本；有什么问题吗:

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