Find invalid objects или готовим своими руками…


Административные скрипты MS SQL > Find invalid objects или готовим своими руками…
22.08.2016 12:03:40


Наиболее часто встречающиеся слова в статье:

[obj_name] [obj_type] [objects] [object_id] [SCHEMA_NAME] [err_message] [schema_id] [referenced_schema_name] [referenced_entity_name]


Статья:

Источник : https://habrahabr.ru/post/222397/

В обязанности администратора баз данных входит много разных задач, которые, в основном, направлены на поддержку работоспособности и целостности базы данных. И если целостность данных можно проверить через команду CHECKDB, то с поиском невалидных объектов в схеме не все так гладко.

Если проводить аналогии с Oracle, то в SQL Serverнельзя так же легко получить список невалидных объектов:

SELECT owner, object_type, object_name
FROM all_objects
WHERE status = 'INVALID'

В большинстве ситуаций, узнать о том, что скриптовый объект является невалидным, можно только при его выполнении. Конечно, такое положение дел, может не всех устроить, поэтому предлагаю написать скрипт по поиску невалидных объектов в базе данных.

SELECT
      obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
    , obj_type = o.type_desc
    , d.referenced_database_name
    , d.referenced_schema_name
    , d.referenced_entity_name
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON d.referencing_id = o.[object_id]
WHERE d.is_ambiguous = 0
    AND d.referenced_id IS NULL -- если не можем определить от какого объекта зависимость
    AND d.referenced_server_name IS NULL -- игнорируем объекты с Linked server
    AND CASE d.referenced_class -- если не существует
        WHEN 1 -- объекта
            THEN OBJECT_ID(
                ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' + 
                ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' + 
                QUOTENAME(d.referenced_entity_name))
        WHEN 6 -- или типа данных
            THEN TYPE_ID(
                ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name) 
        WHEN 10 -- или XML схемы
            THEN (
                SELECT 1 FROM sys.xml_schema_collections x 
                WHERE x.name = d.referenced_entity_name
                    AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID())
                )
        END IS NULL

Для первичной диагностики данный запрос меня не раз выручал. Тем не менее, он не лишен недостатков. Пожалуй, самый главный из них – данный запрос не будет показывать объекты, где встречаются невалидные столбцы или параметры:

CREATE VIEW dbo.vw_View
AS SELECT ID = 1
GO

CREATE PROCEDURE dbo.usp_Procedure
AS BEGIN
    SELECT ID FROM dbo.vw_View
END
GO

ALTER VIEW dbo.vw_View
AS SELECT New_ID = 1
GO

При выполнении хранимой процедуры мы получим ошибку:

Msg 207, Level 16, State 1, Procedure usp_Procedure, Line 6
Invalid column name 'ID'.

Кроме того, на SQL Server 2005, приведенный выше запрос работать не будет. Поскольку там для нахождения зависимостей используются другие системные представления, которые к тому же могут показывать только валидные зависимости для объекта.

В силу этих причин, в качестве основного рабочего варианта, предлагаемый запрос не сильно целесообразно использовать. Но не все потеряно, поскольку в арсенале SQL Server есть системная процедура для принудительного обновления зависимостей скриптового объекта – sp_refreshsqlmodule.

В случае, если скриптовый объект содержит какой-либо невалидный объект – эта процедура сгенерирует ошибку. Самый очевидный вариант — в курсоре вызывать эту процедуру для каждого объекта и если она завершилась с ошибкой, то помечать такой объект как невалидный. 

Кроме того, не стоит забывать, что скриптовые объекты могут не иметь зависимостей. Либо могут изначально не содержать невалидных объектов, например, представления, созданные с опцией SCHEMABINDING или скалярные функции, которые используются в DEFAULT или CHECK констрейнтах и в COMPUTED столбцах. Для таких объектов проверку на валидность нецелесообразно проводить — это контролирует SQL Server.

Предлагаемый скрипт для поиска невалидных объектов, с учетом специфики SQL Server 2008/2012/2014:

SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#objects') IS NOT NULL
    DROP TABLE #objects

CREATE TABLE #objects (
      obj_id INT PRIMARY KEY
    , obj_name NVARCHAR(261)
    , err_message NVARCHAR(2048) NOT NULL
    , obj_type CHAR(2) NOT NULL
)

INSERT INTO #objects (obj_id, obj_name, err_message, obj_type)
SELECT 
      t.referencing_id
    , obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
    , 'Invalid object name ''' + t.obj_name + ''''
    , o.[type]
FROM (
    SELECT
          d.referencing_id
        , obj_name = MAX(COALESCE(d.referenced_database_name + '.', '') 
                + COALESCE(d.referenced_schema_name + '.', '') 
                + d.referenced_entity_name)
    FROM sys.sql_expression_dependencies d
    WHERE d.is_ambiguous = 0
        AND d.referenced_id IS NULL -- если не можем определить от какого объекта зависимость
        AND d.referenced_server_name IS NULL -- игнорируем объекты с Linked server
        AND CASE d.referenced_class -- если не существует
            WHEN 1 -- объекта
                THEN OBJECT_ID(
                    ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' + 
                    ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' + 
                    QUOTENAME(d.referenced_entity_name))
            WHEN 6 -- или типа данных
                THEN TYPE_ID(
                    ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name) 
            WHEN 10 -- или XML схемы
                THEN (
                    SELECT 1 FROM sys.xml_schema_collections x 
                    WHERE x.name = d.referenced_entity_name
                        AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID())
                    )
            END IS NULL
    GROUP BY d.referencing_id
) t
JOIN sys.objects o ON t.referencing_id = o.[object_id]
WHERE LEN(t.obj_name) > 4 -- чтобы не показывать валидные алиасы, как невалидные объекты

DECLARE
      @obj_id INT
    , @obj_name NVARCHAR(261)
    , @obj_type CHAR(2)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT
          sm.[object_id]
        , QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
        , o.[type]
    FROM sys.sql_modules sm
    JOIN sys.objects o ON sm.[object_id] = o.[object_id]
    LEFT JOIN (
        SELECT s.referenced_id
        FROM sys.sql_expression_dependencies s
        JOIN sys.objects o ON o.object_id = s.referencing_id
        WHERE s.is_ambiguous = 0
            AND s.referenced_server_name IS NULL
            AND o.[type] IN ('C', 'D', 'U')
        GROUP BY s.referenced_id
    ) sed ON sed.referenced_id = sm.[object_id]
    WHERE sm.is_schema_bound = 0 -- объект создан без опции WITH SCHEMABINDING
        AND sm.[object_id] NOT IN (SELECT o2.obj_id FROM #objects o2) -- чтобы повторно не определять невалидные объекты
        AND OBJECTPROPERTY(sm.[object_id], 'IsEncrypted') = 0
        AND (
              o.[type] IN ('IF', 'TF', 'V', 'TR')
            -- в редких случаях, sp_refreshsqlmodule может портить метаданные хранимых процедур (Bug #656863)
            --OR o.[type] = 'P' 
            OR (
                   o.[type] = 'FN'
                AND
                   -- игнорируем скалярные функции, которые используются в DEFAULT/CHECK констрейнтах и в COMPUTED столбцах
                   sed.referenced_id IS NULL
            )
       )

OPEN cur

FETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_type

WHILE @@FETCH_STATUS = 0 BEGIN

    BEGIN TRY

        BEGIN TRANSACTION
            EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT' 
        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION

        INSERT INTO #objects (obj_id, obj_name, err_message, obj_type) 
        SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type

    END CATCH

    FETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_type

END

CLOSE cur
DEALLOCATE cur

SELECT obj_name, err_message, obj_type
FROM #objects

На SQL Server 2005 это же скрипт будет таким:

SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#objects') IS NOT NULL
    DROP TABLE #objects

CREATE TABLE #objects (
      obj_name NVARCHAR(261)
    , err_message NVARCHAR(2048) NOT NULL
    , obj_type CHAR(2) NOT NULL
)

DECLARE
      @obj_name NVARCHAR(261)
    , @obj_type CHAR(2)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT
          QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
        , o.[type]
    FROM sys.sql_modules sm
    JOIN sys.objects o ON sm.[object_id] = o.[object_id]
    LEFT JOIN (
        SELECT s.referenced_major_id
        FROM sys.sql_dependencies s
        JOIN sys.objects o ON o.object_id = s.[object_id]
        WHERE o.[type] IN ('C', 'D', 'U')
        GROUP BY s.referenced_major_id
    ) sed ON sed.referenced_major_id = sm.[object_id]
    WHERE sm.is_schema_bound = 0 -- объект создан без опции WITH SCHEMABINDING
        AND OBJECTPROPERTY(sm.[object_id], 'IsEncrypted') = 0
        AND (
              o.[type] IN ('IF', 'TF', 'V', 'TR')
            -- в редких случаях, sp_refreshsqlmodule может портить метаданные хранимых процедур (Bug #656863)
            --OR o.[type] = 'P' 
            OR (
                   o.[type] = 'FN'
                AND
                   -- игнорируем скалярные функции, которые используются в DEFAULT/CHECK констрейнтах и в COMPUTED столбцах
                   sed.referenced_major_id IS NULL 
            )
       )

OPEN cur

FETCH NEXT FROM cur INTO @obj_name, @obj_type

WHILE @@FETCH_STATUS = 0 BEGIN

    BEGIN TRY

        BEGIN TRANSACTION
            EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT' 
        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION

        INSERT INTO #objects (obj_name, err_message, obj_type) 
        SELECT @obj_name, ERROR_MESSAGE(), @obj_type

    END CATCH

    FETCH NEXT FROM cur INTO @obj_name, @obj_type

END

CLOSE cur
DEALLOCATE cur

SELECT obj_name, err_message, obj_type
FROM #objects

Для примера, приведу результаты выполнения скрипта на тестовой базе:

obj_name                          err_message                                                                      obj_type
--------------------------------- -------------------------------------------------------------------------------  --------
[dbo].[vw_EmployeePersonalInfo]   An insufficient number of arguments were supplied for 'dbo.GetEmployee'          V 
[dbo].[udf_GetPercent]            Invalid column name 'Code'.                                                      FN
[dbo].[trg_AIU_Sync]              Invalid column name 'DateOut'.                                                   P
[dbo].[trg_IOU_SalaryEmployee]    Invalid object name 'dbo.tbl_SalaryEmployee'.                                    TR
[dbo].[trg_IU_ReturnDetail]       The object 'dbo.ReturnDetail' does not exist or is invalid for this operation.   TR
[dbo].[ReportProduct]             Invalid object name 'dbo.ProductDetail'.                                         IF

Теперь пару слов о синонимах. При их создании SQL Server не валидирует имя объекта. На практике получается, что синоним можно создать на несуществующий объект. Чтобы найти все невалидные синонимы можно воспользоваться следующим простым запросом:

SELECT QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + QUOTENAME(s.name)
FROM sys.synonyms s
WHERE PARSENAME(s.base_object_name, 4) IS NULL -- игнорируем объекты с Linked server
    AND OBJECT_ID(s.base_object_name) IS NULL

Если возникнет необходимость, добавить к существующим запросам проверку на невалидные синонимы можно так:

...
SELECT obj_name, err_message, obj_type
FROM #objects

UNION ALL

SELECT 
      QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + QUOTENAME(s.name)
    , 'Invalid object name ''' + s.base_object_name + ''''
    , s.[type]
FROM sys.synonyms s
WHERE PARSENAME(s.base_object_name, 4) IS NULL
    AND OBJECT_ID(s.base_object_name) IS NULL