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