Find invalid objects

  • Tutorial
В обязанности администратора баз данных входит много разных задач, которые, в основном, направлены на поддержку работоспособности и целостности базы данных. И если целостность данных можно проверить через команду 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

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

Если хотите поделиться этой статьей с англоязычной аудиторией:
Find invalid objects in your databases
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 14

    +1
    К минусам неплохо было-бы прикреплять свои комментарии :)
      +4
      Я не специалист по базам данным. Мне не понятно, что такое не валидные объекты зачем их надо искать.
        +1
        Это объекты в базе данных, которые там созданы, лежат себе такие спокойно, например хранимые процедуры. По прошествии времени кто-то поменял таблицу, с которой работала эта процедура. Допустим удалил колонку. Вроде бы в базе есть и хранимая процедура и таблица. Но колонки не стало. Там нет перекомпиляции:) Вот вам и ошибка времени выполнения. А таких мест может быть не одна сотня. Хотя, конечно, есть механизмы отображения зависимостей и все это надо учитывать (и многое другое), но так бывает.
          0
          спасибо, теперь все стало на свои места :)
          0
          Поддерживаю. «невалидные объекты» — термин, применимый не ко всем СУБД, так что хотелось бы понять, где они есть и что они собой представляют. В MySQL, Postgres, SQLite я не помню такой терминологии.
            0
            Точно знаю, что невалидные объекты есть в Oracle и SQL Server, поскольку на уровне метаданных есть соответствующие признаки. Например, Oracle переводит объекты в состояние INVALID, если зависимый объект изменяется. Если следующая компиляция проходит успешно, то объект становится помечается как VALID.
            0
            На уровне метаданных, в SQL Server существуют понятия soft и hard зависимостей. Последние всегда валидируют связанные объекты при их изменении или попытке удаления.

            Пример таких зависимостей – скалярная функция, которая используется в COMPUTED столбце:

            CREATE FUNCTION dbo.udf_Computed (@a INT)
            RETURNS INT
            AS BEGIN
            	RETURN @a
            END
            GO
            
            CREATE TABLE dbo.tbl_Table (
                ID INT,
                Value AS dbo.udf_Computed(ID)
            )
            GO
            
            DROP FUNCTION dbo.udf_Computed
            GO
            

            Msg 3729, Level 16, State 1, Line 2
            Cannot DROP FUNCTION 'dbo.udf_Computed' because it is being referenced by object 'tbl_Table'.
            

            В свою очередь, soft зависимости таким свойством не обладают. На практике же, существует большое количество нюансов с которыми я столкнулся, когда реализовывал поиск зависимостей в этом туле. Если будет желание могу описать такие случаи в отдельном посте по мере появления свободного времени.

            +1
            Первый скрипт на 2008r2 мне отчего-то выдал
            Msg 3930, Level 16, State 1, Line 95
            Текущая транзакция не может быть зафиксирована и не может поддерживать операции, производящие запись в файл журнала. Выполните откат транзакции.

            много раз :)

            Падает в:
                BEGIN TRY
                    EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT' 
                END TRY
                BEGIN CATCH
                    INSERT INTO #objects (obj_id, obj_name, err_message, obj_type) 
                    SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type
                END CATCH
            


            на INSERT.
              0
              В Management Studio после выполнения запроса миниум 2 закладки. Results и Messages. Откройте Результаты уже скорее:) В Messages выводит SELECT в самом скрипте.
                0
                Предупреждать надо :) Я привык не доверять результатам, если в messages куча ошибок :)
                Плюсануть не могу, потому спасибо так.
                  0
                  Меня это тоже настораживает.
                0
                Спасибо за комментарий. Я проверю в чем может быть проблема и обновлю пост.
                  0
                  Попробуйте заменить блок:

                  BEGIN TRY
                      EXEC sys.sp_refreshsqlmodule @name = @obj_name
                  END TRY
                  BEGIN CATCH
                      INSERT INTO #objects (obj_name, err_message, obj_type) 
                      SELECT @obj_name, ERROR_MESSAGE(), @obj_type
                  END CATCH
                  

                  на вот такой:

                  BEGIN TRY
                  
                  	BEGIN TRANSACTION
                  		EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT' 
                  	COMMIT TRANSACTION
                  
                  END TRY
                  BEGIN CATCH
                  	INSERT INTO #objects (obj_id, obj_name, err_message, obj_type) 
                  	SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type
                  
                  	IF XACT_STATE() <> 0
                  		ROLLBACK TRANSACTION
                  END CATCH
                  
                    +1
                    Теперь ошибка всего одна:
                    Msg 3930, Level 16, State 1, Line 99
                    Текущая транзакция не может быть зафиксирована и не может поддерживать операции, производящие запись в файл журнала. Выполните откат транзакции.

                    Справедливо происходит на
                    INSERT INTO #objects (obj_id, obj_name, err_message, obj_type) 
                        SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type
                    

                    ибо предыдущая транзакция незафиксирована. Т.е. по идее, ее надо откатить раньше.
                    Но если собрать блок catch вот так:
                    	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
                    
                    
                    то проблем не возникает.

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое