📆 🥩 👦 अमान्य ऑब्जेक्ट खोजें 🗓️ 🔘 📸

डेटाबेस व्यवस्थापक की जिम्मेदारियों में कई अलग-अलग कार्य शामिल हैं, जो मुख्य रूप से डेटाबेस के स्वास्थ्य और अखंडता का समर्थन करने के उद्देश्य से हैं। और अगर CHECKDB कमांड के माध्यम से डेटा अखंडता की जांच की जा सकती है, तो योजना में अमान्य वस्तुओं की खोज के साथ, सब कुछ इतना आसान नहीं है।

यदि हम Oracle के साथ उपमाएँ बनाते हैं, तो SQL सर्वर में आप आसानी से अवैध वस्तुओं की सूची प्राप्त नहीं कर सकते हैं:

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 सर्वर शस्त्रागार में एक स्क्रिप्टेड ऑब्जेक्ट की निर्भरता को जबरन अपडेट करने के लिए एक सिस्टम प्रक्रिया है - sp_refreshsqlmodule ।

यदि स्क्रिप्ट की गई ऑब्जेक्ट में कुछ अमान्य ऑब्जेक्ट हैं, तो यह प्रक्रिया एक त्रुटि उत्पन्न करेगी। सबसे स्पष्ट विकल्प कर्सर में प्रत्येक ऑब्जेक्ट के लिए इस प्रक्रिया को कॉल करना है, और यदि यह विफल रहता है, तो ऑब्जेक्ट को अमान्य के रूप में चिह्नित करें।

इसके अलावा, यह मत भूलो कि स्क्रिप्ट ऑब्जेक्ट्स पर निर्भरता नहीं हो सकती है। या उनमें प्रारंभ में अमान्य ऑब्जेक्ट नहीं हो सकते हैं, उदाहरण के लिए, SCHEMABINDING विकल्प या स्केलर फ़ंक्शंस के साथ बनाए गए विचार जो DEFAULT या CHECK स्थिरांक और COMPUTED कॉलम में उपयोग किए जाते हैं। ऐसी वस्तुओं के लिए, सत्यापन जांच व्यावहारिक नहीं है - यह SQL सर्वर द्वारा नियंत्रित किया जाता है।

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 सर्वर ऑब्जेक्ट नाम को मान्य नहीं करता है। व्यवहार में, यह पता चला है कि एक गैर-मौजूद वस्तु पर एक समानार्थी शब्द बनाया जा सकता है। सभी अमान्य समानार्थी शब्दों को खोजने के लिए, आप निम्नलिखित सरल क्वेरी का उपयोग कर सकते हैं:

 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

जैसा कि आप देख सकते हैं, कुशलता से मेटाडेटा से जानकारी का उपयोग करके, आप कार्यक्षमता को लागू कर सकते हैं जो डेटाबेस रखरखाव और निगरानी के रोजमर्रा के कार्यों को हल करने के लिए पर्याप्त नहीं है।

यदि आप इस लेख को अंग्रेजी बोलने वाले दर्शकों के साथ साझा करना चाहते हैं:

अपने डेटाबेस में अमान्य ऑब्जेक्ट खोजें

अमान्य ऑब्जेक्ट खोजें

More articles: