Найдите поврежденные объекты в SQL Server

Вас могут заинтересовать следующие статьи:

• Майкл Дж. Сварт: Найти недостающие зависимости SQL
• eggheadcafe.com : Найти сломанные вещи

Вы можете проверить решение Майкла Дж. Сварта следующим образом:

CREATE PROCEDURE proc_bad AS
    SELECT col FROM nonexisting_table
GO

SELECT
    OBJECT_NAME(referencing_id) AS [this sproc or VIEW...],
    referenced_entity_name AS [... depends ON this missing entity name]
FROM 
    sys.sql_expression_dependencies
WHERE 
    is_ambiguous = 0
    AND OBJECT_ID(referenced_entity_name) IS NULL
ORDER BY 
    OBJECT_NAME(referencing_id), referenced_entity_name;

Что возвращает:

+------------------------+------------------------------------------+
| this sproc or VIEW...  |  ... depends ON this missing entity name |
|------------------------+------------------------------------------|
| proc_bad               |  nonexisting_table                       |
+------------------------+------------------------------------------+

Два предыдущих решения здесь интересны, но оба не сработали в моих тестовых базах данных.

Оригинальный сценарий Майкла Дж. Сварта произвел для меня огромное количество ложных срабатываний, слишком много, чтобы пробиться через него. Решение Рика В. здесь было лучше - единственные ложные срабатывания, которые он дал, были для ссылок между базами данных.

Есть комментарий к статье Michael J Swart от RaduSun, которая дает решение, которое я пока не могу сломать! Вот и все, слегка подправленные для удобства чтения и моих целей, но благодарность RaduSun за логику.

SELECT 
    QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' 
        + QuoteName(OBJECT_NAME(referencing_id)) AS ProblemObject,
    o.type_desc,
    ISNULL(QuoteName(referenced_server_name) + '.', '')
    + ISNULL(QuoteName(referenced_database_name) + '.', '')
    + ISNULL(QuoteName(referenced_schema_name) + '.', '')
    + QuoteName(referenced_entity_name) AS MissingReferencedObject
FROM
    sys.sql_expression_dependencies sed
        LEFT JOIN sys.objects o
            ON sed.referencing_id=o.object_id
WHERE
    (is_ambiguous = 0)
    AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '')
    + ISNULL(QuoteName(referenced_database_name) + '.', '')
    + ISNULL(QuoteName(referenced_schema_name) + '.', '')
    + QuoteName(referenced_entity_name)) IS NULL)
ORDER BY
    ProblemObject,
    MissingReferencedObject

Подсказка 5 для Red Gate Software содержит функцию Поиск недопустимых объектов , которая может быть полезна в этой ситуации. Инструмент выполняет поиск в базе данных объектов, которые при запуске выдают ошибку, которая звучит именно так, как вы хотите.

Вы можете бесплатно скачать 14-дневную пробную версию, чтобы попробовать и посмотреть, поможет ли она.

Пол Стивенсон
Руководитель проекта SQL Prompt
Программное обеспечение Red Gate

First query даст вам сломанные объекты, имя которых включает в себя Stored Procedure, View, Scalar function, DML trigger, Table-valued-function тип

/*
/////////////
////ERROR////
/////////////
All error will be listed if object is broken
*/
DECLARE @AllObjectName TABLE (
    OrdinalNo INT IDENTITY
    ,ObjectName NVARCHAR(MAX)
    ,ObjectType NVARCHAR(MAX)
    ,ErrorMessage NVARCHAR(MAX)
    )

INSERT INTO @AllObjectName (
    ObjectName
    ,ObjectType
    )
SELECT '[' + SCHEMA_NAME(schema_id) + '].[' + NAME + ']' ObjectName
    ,CASE [TYPE]
        WHEN 'P'
            THEN 'Stored Procedure'
        WHEN 'V'
            THEN 'View'
        WHEN 'FN'
            THEN 'Scalar function'
        WHEN 'TR'
            THEN 'DML trigger'
        WHEN 'TF'
            THEN 'Table-valued-function'
        ELSE 'Unknown Type'
        END
FROM sys.objects
WHERE [TYPE] IN (
        'P'
        ,'V'
        ,'FN'
        ,'TR'
        ,'TF'
        )
ORDER BY NAME

DECLARE @i INT = 1
DECLARE @RowCount INT = (
        SELECT count(1)
        FROM @AllObjectName
        )
DECLARE @ObjectName VARCHAR(MAX)

WHILE @i <= @RowCount
BEGIN
    BEGIN TRY
        SET @ObjectName = (
                SELECT ObjectName
                FROM @AllObjectName
                WHERE OrdinalNo = @i
                )

        EXEC sys.sp_refreshsqlmodule @ObjectName
    END TRY

    BEGIN CATCH
        DECLARE @message VARCHAR(4000)
            ,@xstate INT;

        SELECT @message = ERROR_MESSAGE()
            ,@xstate = XACT_STATE();

        IF @xstate = - 1
            ROLLBACK;

        UPDATE @AllObjectName
        SET ErrorMessage = @message
        WHERE OrdinalNo = @i
    END CATCH

    SET @i = @i + 1
END

SELECT ObjectName
    ,ObjectType
    ,ErrorMessage
FROM @AllObjectName
WHERE ErrorMessage IS NOT NULL

И below one поиск неразрешенных ссылок. Обычно, который рассматривается как warning, все еще может вызвать error когда-нибудь

/*
/////////////
///Warning///
/////////////
Here all warning will come if object reference is not stated properly
*/
SELECT TOP (100) PERCENT QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS [this Object...]
    ,o.type_desc
    ,ISNULL(QuoteName(referenced_server_name) + '.', '') + ISNULL(QuoteName(referenced_database_name) + '.', '') + ISNULL(QuoteName(referenced_schema_name) + '.', '') + QuoteName(referenced_entity_name) AS [... depends ON this missing entity name]
    ,sed.referenced_class_desc
FROM sys.sql_expression_dependencies AS sed
LEFT JOIN sys.objects o ON sed.referencing_id = o.object_id
WHERE (is_ambiguous = 0)
    AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '') + ISNULL(QuoteName(referenced_database_name) + '.', '') + ISNULL(QuoteName(referenced_schema_name) + '.', '') + QuoteName(referenced_entity_name)) IS NULL)
    AND NOT EXISTS (
        SELECT *
        FROM sys.types
        WHERE types.NAME = referenced_entity_name
            AND types.schema_id = ISNULL(SCHEMA_ID(referenced_schema_name), SCHEMA_ID('dbo'))
        )
ORDER BY [this Object...]
    ,[... depends ON this missing entity name]

Спасибо @SQLMonger .. за предоставление мне подсказка, чтобы сделать First query, который был моим фактическим требованием

/*
modified version of script from http://michaeljswart.com/2009/12/find-missing-sql-dependencies/
Added columns for object types & generated refresh module command...
filter out user-define types: http://stackoverflow.com/questions/2330521/find-broken-objects-in-sql-server

* /

SELECT TOP (100) PERCENT
    QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS [this Object...],
        o.type_desc,
    ISNULL(QuoteName(referenced_server_name) + '.', '')
    + ISNULL(QuoteName(referenced_database_name) + '.', '')
    + ISNULL(QuoteName(referenced_schema_name) + '.', '')
    + QuoteName(referenced_entity_name) AS [... depends ON this missing entity name]
    ,sed.referenced_class_desc
    ,case when o.type_desc in( 'SQL_STORED_PROCEDURE' ,'SQL_SCALAR_FUNCTION' ,'SQL_TRIGGER' ,'VIEW')
          then 'EXEC sys.sp_refreshsqlmodule ''' + QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) + ''';'
          else null
       end as [Refresh SQL Module command]
FROM sys.sql_expression_dependencies as sed
LEFT JOIN sys.objects o
            ON sed.referencing_id=o.object_id
WHERE (is_ambiguous = 0)
AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '')
    + ISNULL(QuoteName(referenced_database_name) + '.', '')
    + ISNULL(QuoteName(referenced_schema_name) + '.', '')
    + QuoteName(referenced_entity_name)) IS NULL)
AND NOT EXISTS
   (SELECT * 
    FROM sys.types 
    WHERE types.name = referenced_entity_name 
    AND types.schema_id = ISNULL(SCHEMA_ID(referenced_schema_name), SCHEMA_ID('dbo'))
   )
ORDER BY [this Object...],
[... depends ON this missing entity name]

Начиная с SQL Server 2008, здесь используется гораздо более простой метод:

 SELECT OBJECT_NAME(referencing_id) AS 'object making reference' ,
       referenced_class_desc ,
       referenced_schema_name ,
       referenced_entity_name AS 'object name referenced' ,
       (   SELECT object_id
           FROM   sys.objects
           WHERE  name = [referenced_entity_name]
       ) AS 'Object Found?'
FROM   sys.sql_expression_dependencies e
       LEFT JOIN sys.tables t ON e.referenced_entity_name = t.name;

Как упомянуто в исходной статье ( Статья Microsoft MSDN по поиску недостающих зависимостей ), «A» Значение NULL в 'объект найден?' столбец указывает, что объект не был найден в sys.objects. "

Пример вывода:

╔═══════════════════════════════════════════════╦═══════════════════════╦════════════════════════╦═══════════════════════════════════════╦═══════════════╗
║            object making reference            ║ referenced_class_desc ║ referenced_schema_name ║        object name referenced         ║ Object Found? ║
╠═══════════════════════════════════════════════╬═══════════════════════╬════════════════════════╬═══════════════════════════════════════╬═══════════════╣
║ usvConversationsWithoutServerNotices          ║ OBJECT_OR_COLUMN      ║ dbo                    ║ ConversationLinesWithID               ║ NULL          ║
║ usvFormattedConversationLines_WithSpeakerName ║ OBJECT_OR_COLUMN      ║ dbo                    ║ ConversationLinesWithID               ║ NULL          ║
║ usvFormattedConversationLines_WithSpeakerName ║ OBJECT_OR_COLUMN      ║ dbo                    ║ FormattedConversationLines_Cached     ║ NULL          ║
║ udpCheckForDuplicates                         ║ OBJECT_OR_COLUMN      ║ dbo                    ║ FormattedConversationLines_WithChatID ║ NULL          ║
║ usvFormattedConversationsCombined             ║ OBJECT_OR_COLUMN      ║ dbo                    ║ GROUP_CONCAT_D                        ║ 178099675     ║
║ usvSequenceCrossValidationSetStudents         ║ OBJECT_OR_COLUMN      ║ dbo                    ║ usvSequenceCrossValidationSet         ║ 1406628054    ║
╚═══════════════════════════════════════════════╩═══════════════════════╩════════════════════════╩═══════════════════════════════════════╩═══════════════╝

Несколько лет назад я написал скрипт, который найдет хранимые процедуры, которые не будут компилироваться, потянув за текст процесса и пытаясь перекомпилировать его с помощью блока try / catch. Это довольно просто и эффективно найти хотя бы процедуры, которые можно отбросить. Вы можете легко расширить его для просмотра.

Обратите внимание, что вы должны запускать это только в среде DEV или TEST, так как она на самом деле пытается перекомпилировать процедуры.

SET NOCOUNT ON

DECLARE @ProcedureName VARCHAR(2048)
DECLARE @ProcedureBody VARCHAR(MAX)

DECLARE @RoutineName varchar(500)

DECLARE procCursor CURSOR STATIC FORWARD_ONLY READ_ONLY
 FOR
 SELECT
 --TOP 1
 SCHEMA_NAME(schema_id) + '.' + NAME AS ProcedureName,
 OBJECT_DEFINITION(o.[object_id]) AS ProcedureBody
 FROM sys.objects AS o
 WHERE o.[type] = 'P'
 ORDER BY o.[name]

OPEN procCursor
FETCH NEXT FROM procCursor INTO @ProcedureName, @ProcedureBody

WHILE @@FETCH_STATUS = 0
BEGIN
 -- Might have to play with this logic if you don't have discipline in your create statements
 SET @ProcedureBody = REPLACE(@ProcedureBody, 'CREATE PROCEDURE', 'ALTER PROCEDURE')

 BEGIN TRY
   EXECUTE(@ProcedureBody)
   PRINT @ProcedureName + ' -- Succeeded'
 END TRY
 BEGIN CATCH
   PRINT @ProcedureName + ' -- Failed: ' + ERROR_MESSAGE()
 END CATCH

 FETCH NEXT FROM procCursor INTO @ProcedureName, @ProcedureBody
END

CLOSE procCursor
DEALLOCATE procCursor

https://brettwgreen.wordpress.com/2012/12/04/find-stored-procedures-that-wont-compile/

Обратите внимание, что запрос в этой теме находит недостающие объекты, а не недействительные. & Nbsp;
SQL Server не находит недопустимый объект ссылки, пока вы его не выполните.

Расширение этого запроса для обработки объектов в других схемах, а также типов:

SELECT
    '[' + OBJECT_SCHEMA_NAME(referencing_id) + '].[' + OBJECT_NAME(referencing_id) + ']' 
        AS [this sproc, UDF or VIEW...],
    isnull('[' + referenced_schema_name + '].', '') + '[' + referenced_entity_name + ']' 
        AS [... depends ON this missing entity name]
FROM 
    sys.sql_expression_dependencies
WHERE 
    is_ambiguous = 0 AND 
    (
        (
            [referenced_class_desc] = 'TYPE' and 
            TYPE_ID(
                isnull('[' + referenced_schema_name + '].', '') + 
                '[' + referenced_entity_name + ']'
            ) IS NULL
        ) or
        (   
            [referenced_class_desc] <> 'TYPE' and 
            OBJECT_ID(
                isnull('[' + referenced_schema_name + '].', '') + 
                '[' + referenced_entity_name + ']'
            ) IS NULL
        )
    )
ORDER BY 
    '[' + OBJECT_SCHEMA_NAME(referencing_id) + '].[' + OBJECT_NAME(referencing_id) + ']',
    isnull('[' + referenced_schema_name + '].', '') + '[' + referenced_entity_name + ']'

 create table #BrokenObjects (Name nvarchar(500), Error nvarchar(max))
 select * into #objects from(
 select name from sys.views
 union select name from sys.procedures
 union select name from sys.tables
 )x
 declare @name nvarchar(500),@err nvarchar(max)
 while exists(select top 1 * from #objects)
 begin
 select top 1 @name = name from #objects
 begin try
 EXEC sys.sp_refreshsqlmodule @name
 end try
 begin catch
 select @err = ERROR_MESSAGE()
 insert into #BrokenObjects (name,error) values (@name,@err)
 end catch
 delete from #objects
 where name = @name
 end
 drop table #objects
 select * from #BrokenObjects
 where Error not like 'Could not find object % or you do not have permission.'

 drop table #BrokenObjects

Лучше всего начать с использования такого инструмента, как Visual Studio Database Edition. Его роль заключается в управлении схемой базы данных. Одна из многих вещей, которые он будет делать, - выдавать ошибку при попытке построить проект базы данных, который содержит битые объекты. Это конечно сделает намного больше чем это. Этот инструмент бесплатен для любого пользователя Visual Studio Team Suite или Visual Studio Developer Edition.