Подтвердить что ты не робот

Поиск сломанных объектов в SQL Server

Есть ли инструмент, который найдет все объекты в SQL Server (функции, procs, views), которые не могут работать, потому что они ссылаются на объекты, которые не существуют?

4b9b3361

ОТВЕТЫ

Ответ 1

На самом деле я использую процедуру sys.refreshmodule, которая теперь завернута в powershell script с добавлением SQL Server Powershell.

Это работает лучше, потому что эта удобная небольшая функция sys избавляется от материала CREATE vs ALTER. Некоторые другие ответы здесь также используют этот подход, но я предпочитаю этот, который завернут в Powershell, и, возможно, некоторые из них посчитают это полезным.

$server = "YourDBServer"
cls
Import-Module "sqlps" -DisableNameChecking

$databases = Invoke-Sqlcmd -Query "select name from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb')" -ServerInstance $server
foreach ($db in $databases) {
    $dbName = $db.name
    $procedures = Invoke-Sqlcmd -Query "select SCHEMA_NAME(schema_id) as [schema], name from $dbName.sys.procedures" -ServerInstance $server
    foreach ($proc in $procedures) {
        if ($schema) {
            $shortName = $proc.schema + "." + $proc.name
            $procName =  $db.name + "." + $shortName
            try {
                $result = Invoke-Sqlcmd -Database $dbName -Query "sys.sp_refreshsqlmodule '$shortName'" -ServerInstance $server -ErrorAction Stop
                Write-Host "SUCCESS|$procName"
            }
            catch {
                $msg = $_.Exception.Message.Replace([Environment]::NewLine, ",")
                Write-Host "FAILED|$procName|$msg" -ForegroundColor Yellow
            }
        }
    }
}

Ответ 2

Вам может быть интересно узнать следующие статьи:

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

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                       |
+------------------------+------------------------------------------+

Ответ 3

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

Оригинальный Michael J Swart script произвел огромное количество ложных срабатываний для меня, слишком много, чтобы пробраться. Решение Рика V. здесь было лучше - единственные ложные срабатывания, которые он давал, были для ссылок между базами данных.

Там есть комментарий к статье Михаэля 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

Ответ 4

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

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

Пол Стивенсон
SQL Prompt Project Manager
Программное обеспечение Red Gate

Ответ 5

First query даст вам сломанные имена объектов: Stored Procedure, View, Scalar function, DML trigger, Table-valued-function type

/*
/////////////
////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 sometime

/*
/////////////
///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, который был моим фактическим требованием

Ответ 6

/*
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]

Ответ 7

В 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 Article on Finding Missing Dependencies), "Значение" NULL "в" Объекте найдено? "column указывает, что объект не найден в 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    ║
╚═══════════════════════════════════════════════╩═══════════════════════╩════════════════════════╩═══════════════════════════════════════╩═══════════════╝

Ответ 8

Несколько лет назад я написал script, который найдет хранимые процедуры, которые не будут скомпилированы, вытащив текст proc и попытавшись перекомпилировать его с помощью блока 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/

Ответ 9

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

Ответ 10

Обратите внимание, что запрос в этом потоке находит отсутствующие объекты, а не недопустимые.
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 + ']'

Ответ 11

 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