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