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