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

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

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