تعيين اعتبار كردن يك عبارت SQL - قسمت دوم

مطلبي را روز قبل نوشتم در مورد تعيين اعتبار يك كوئري. اين مورد از آنجايي حائز اهميت مي‌شود كه براي مثال تغييري در ساختار يكي از جداول حاصل شود. اكنون مي‌خواهيم بررسي كنيم آيا سيستم از كار افتاده يا نه!؟
شما مي‌توانيد نام يك فيلد را تغيير دهيد (حتي اگر اين فيلد در يك رويه ذخيره شده استفاده شده باشد) و هيچ خطايي هم نخواهيد گرفت و اين منشاء دردسرهاي زيادي خواهد بود.
در حالت استفاده از SET NOEXEC ON ، كوئري مورد نظر فقط كامپايل مي‌شود و همچنين از لحاظ نحوي بررسي خواهد شد، اما اين كافي نيست.
مثال زير را در نظر بگيريد:

Create PROCEDURE Test1
AS
SELECT * FROM tblPIDs1

جدول tblPIDs1 در ديتابيس مورد نظر وجود ندارد.
اين كوئري قابل اجرا است. دكمه‌ي F5 را فشار دهيد، بلافاصله رويه ذخيره شده‌ي Test1 براي شما ايجاد خواهد شد.
سپس كوئري زير را اجرا كنيد:

USE testdb
SET NOEXEC ON;
exec test1 ;
SET NOEXEC OFF;

بدون مشكل و بروز خطايي، پيغام زير را نشان مي‌دهد:

Command(s) completed successfully

ايرادي هم وارد نيست چون فقط عمليات parsing و compile صورت گرفته و نه اجراي واقعي رويه ذخيره شده. اينجا از لحاظ دستوري مشكلي وجود ندارد.

در اين نوع موارد مي‌توان از SET FMTONLY ON استفاده كرد. اين مورد اجراي غير واقعي يك كوئري را سبب مي‌شود (تاثيري روي ديتابيس موجود نخواهد داشت، براي مثال اگر در رويه ذخيره شما عبارت insert وجود داشت، ديتايي insert نخواهد شد) و تنها متاديتاي حاصل را بازگشت مي‌دهد. مثلا نام ستون‌هاي يك كوئري را و همچنين در اين حين اگر خطايي رخ داده باشد، آن‌را نيز ارائه خواهد داد.

USE testdb
SET FMTONLY ON;
exec test1 ;
SET FMTONLY OFF;

با اجراي كوئري فوق خطاي زير ظاهر مي‌شود:

Msg 208, Level 16, State 1, Procedure test1, Line 3
Invalid object name 'tblPIDs1'.

براي اتوماسيون اين توانايي مي‌توان از كوئري زير استفاده كرد:

USE  testdb;

SET NOCOUNT ON;

DECLARE @name NVARCHAR(MAX),
       @sql NVARCHAR(MAX),
       @type CHAR(2), -- object type
       @type_desc NVARCHAR(60), -- object type description
       @params NVARCHAR(MAX) -- parameters 
      
DECLARE @tblInvalid TABLE (
           -- invalid objects
           [type_desc] NVARCHAR(60),
           [name] NVARCHAR(MAX),
           [error_number] INT,
           [error_message] NVARCHAR(MAX),
           [type] CHAR(2)
       );       

DECLARE testSPs CURSOR FAST_FORWARD
FOR
   SELECT [name] = OBJECT_NAME(SM.[object_id]),
          [type] = SO.[type],
          SO.[type_desc],
          [params] = (
              SELECT (
                         SELECT CONVERT(
                                    XML,
                                    (
                                        SELECT STUFF(
                                                   (
                                                       SELECT ', ' + [name] +
                                                              '=NULL' AS
                                                              [text()]
                                                       FROM   sys.parameters
                                                       WHERE  [object_id] = SM.[object_id]
                                                              FOR XML PATH('')
                                                   ),
                                                   1,
                                                   1,
                                                   ''
                                               )
                                    )
                                )
                                FOR XML RAW,
                                TYPE
                     ).value('/row[1]', 'varchar(max)')
          )
   FROM   sys.sql_modules SM
          JOIN sys.objects SO
               ON  SO.[object_id] = SM.[object_id]
   WHERE  SO.[is_ms_shipped] = 0
          AND SO.[type] = 'P'


OPEN testSPs 
FETCH NEXT FROM testSPs INTO @name, @type, @type_desc, @params
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
   BEGIN TRY
       SET @sql = 'SET FMTONLY ON; exec ' + @name + ' ' + @params +
           '; SET FMTONLY OFF;'
       --PRINT @sql;      
       EXEC (@sql) ;
   END TRY
   BEGIN CATCH
       PRINT @type_desc + ', ' + @name + ', Error: ' + CAST(ERROR_NUMBER() AS VARCHAR)
       + ', ' + ERROR_MESSAGE();
       INSERT INTO @tblInvalid
       SELECT @type_desc,
              @name,
              ERROR_NUMBER(),
              ERROR_MESSAGE(),
              @type
      
       ;
   END CATCH
  
  
   FETCH NEXT FROM testSPs INTO @name, @type, @type_desc, @params
END 
CLOSE testSPs  
DEALLOCATE testSPs


SELECT [type_desc],
      [name],
      [error_number],
      [error_message]
FROM   @tblInvalid
ORDER BY
      CHARINDEX([type], ' U V PK UQ F TR FN TF P SQ '),
     [name];

توضيحات:
اين كوئري، در ديتابيس جاري كه در قسمت use dbname مشخص مي‌شود، تمامي رويه‌هاي ذخيره شده را به صورت خودكار پيدا مي‌كند. سپس ليست آرگومان‌هاي آن‌ها را نيز يافته و عبارت exec مربوطه را تشكيل مي‌دهد. سپس با استفاده از SET FMTONLY ON سعي در شبيه سازي اجراي تك تك رويه‌هاي ذخيره شده مي‌كند. اگر خطايي در اين بين رخ داد، آن‌ها را در يك جدول موقتي ذخيره كرده و در آخر نتيجه را نمايش مي‌دهد.

ارزش اين كوئري زماني مشخص مي‌شود كه تعداد زيادي رويه ذخيره شده داشته باشيد اما نمي‌دانيد كداميك از آن‌ها بر اساس آخرين تغييرات صورت گرفته، هنوز معتبر هستند يا نه. آيا به قول معروف، سيستم اومد پايين يا خير!؟

نكته:
قسمتي كه از XML استفاده شده جهت concatenating نتيجه حاصل از كوئري، مورد استفاده قرار گرفته و اين روزها بحث رايجي است كه در بسياري از سايت‌ها در مورد آن مي‌توان مطالب مفيدي را يافت. راه ديگر انجام آن استفاده از COALESCE مي‌باشد.


مآخذ:
Check Validity of SQL Server Stored Procedures
Which of your Stored Procedures are no longer Valid
SET FMTONLY ON