データベース管理者の責任には、主にデータベースの健全性と整合性を維持することを目的としたさまざまなタスクが含まれます。 また、 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では、上記のクエリは機能しません。 そこでは他のシステム表現が使用されて依存関係が検出されるため、さらにオブジェクトの有効な依存関係のみを表示できます。
これらの理由により、主な作業オプションとして、提案されたリクエストを使用することはあまりお勧めできません。 ただし、すべてが失われるわけではありません。SQLServerのアーセナルには、スクリプトオブジェクトの依存関係を強制的に更新するシステムプロシージャ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
ご覧のとおり、メタデータの情報を巧みに使用して、データベースの保守と監視の日常的なタスクを解決するには不十分な機能を実装できます。
この記事を英語圏の聴衆と共有したい場合：
データベース内の無効なオブジェクトを見つける