کشف وابستگی اشیا SQL Server (dependency)

کشف دقیق وابستگی‌های واقعی اشیاء(dependency) در SQL Server: راهنمای جامع

مدیریت و نگهداری پایگاه‌های داده SQL Server بدون درک صحیح از وابستگی‌های اشیاء(dependency)، می‌تواند چالش‌برانگیز باشد. شناسایی دقیق اینکه یک View، Stored Procedure، Function یا Trigger به کدام Table، View یا Function دیگر وابسته است، برای تغییرات، بازسازی (Refactoring) و اطمینان از سلامت سیستم حیاتی است. این راهنما به شما کمک می‌کند تا با استفاده از ابزارهای قدرتمند SQL Server، به بهترین شکل وابستگی‌های واقعی اشیاء پایگاه داده خود را کشف کنید.

استفاده از sys.sql_expression_dependencies

اولین و قدرتمندترین ابزار برای کشف وابستگی‌ها، کاتالوگ ویو sys.sql_expression_dependencies است. این ویو اطلاعات دقیقی در مورد وابستگی‌های نام‌گذاری شده بین اشیاء پایگاه داده ارائه می‌دهد. این اطلاعات شامل وابستگی‌های اشیاء با طرح‌بندی (Schema-bound) و بدون طرح‌بندی (Non-schema-bound) است.

برای مشاهده تمام وابستگی‌ها در پایگاه داده فعلی، می‌توانید از این کوئری استفاده کنید:

SELECT
    OBJECT_NAME(referencing_id) AS ReferencingObject,
    referencing_minor_id,
    referencing_class_desc,
    referenced_server_name,
    referenced_database_name,
    referenced_schema_name,
    referenced_entity_name,
    referenced_class_desc,
    is_caller_dependent
FROM sys.sql_expression_dependencies;

این کوئری اطلاعاتی در مورد شیء ارجاع‌دهنده (referencing object)، شیء مرجع (referenced object)، نوع وابستگی و بسیاری جزئیات دیگر را نمایش می‌دهد. ستون‌های referencing_id و referenced_id کلیدهای اصلی برای شناسایی اشیاء هستند و ستون referenced_entity_name نام شیء مورد ارجاع را نشان می‌دهد.

برای مثال، اگر می‌خواهید وابستگی‌های یک Stored Procedure خاص را پیدا کنید، می‌توانید کوئری را فیلتر کنید. فرض کنید نام Stored Procedure شما MyProcedure است:

SELECT
    OBJECT_NAME(sed.referencing_id) AS ReferencingObject,
    OBJECT_NAME(sed.referenced_id) AS ReferencedObject,
    sed.referenced_entity_name AS ReferencedEntityName,
    sed.referenced_class_desc AS ReferencedClassDescription
FROM sys.sql_expression_dependencies AS sed
WHERE sed.referencing_id = OBJECT_ID('MyProcedure');

این کوئری تمام اشیائی را که MyProcedure به آن‌ها ارجاع می‌دهد، نشان می‌دهد. همچنین می‌توانید به صورت معکوس، اشیائی را پیدا کنید که به یک Table یا View خاص ارجاع می‌دهند. مثلاً، برای پیدا کردن تمام اشیائی که به جدول Customers وابسته هستند:

SELECT
    OBJECT_NAME(sed.referencing_id) AS ReferencingObject,
    sed.referencing_class_desc AS ReferencingClassDescription,
    OBJECT_NAME(sed.referenced_id) AS ReferencedObject,
    sed.referenced_entity_name AS ReferencedEntityName
FROM sys.sql_expression_dependencies AS sed
WHERE sed.referenced_id = OBJECT_ID('Customers');

این قابلیت برای شناسایی تأثیرات جانبی قبل از حذف یا تغییر یک شیء بسیار ارزشمند است و به مدیریت بهتر پایگاه داده کمک می‌کند.

استفاده از sys.dm_sql_referenced_entities و sys.dm_sql_referencing_entities
کاتالوگ ویوهای sys.dm_sql_referenced_entities و sys.dm_sql_referencing_entities توابع مدیریت پویا (Dynamic Management Functions – DMFs) هستند که اطلاعات وابستگی را برای یک شیء مشخص ارائه می‌دهند. این توابع در مواردی که نیاز به بررسی وابستگی‌های یک شیء واحد دارید، بسیار مفیدند و عملکرد مشابهی با sys.sql_expression_dependencies اما با ورودی پارامتریک دارند.

برای یافتن اشیائی که توسط یک Stored Procedure خاص (مثلاً MyProcedure) ارجاع داده می‌شوند:

SELECT
    referenced_database_name,
    referenced_schema_name,
    referenced_entity_name,
    referenced_class_desc
FROM sys.dm_sql_referenced_entities('MyProcedure', 'OBJECT');

و برای یافتن اشیائی که به یک Table یا View خاص (مثلاً Customers) ارجاع می‌دهند:

SELECT
    referencing_id,
    OBJECT_NAME(referencing_id) AS ReferencingObject,
    referencing_class_desc
FROM sys.dm_sql_referencing_entities('Customers', 'OBJECT');

توجه داشته باشید که ورودی دوم (‘OBJECT’) نوع شیئی را که در حال بررسی آن هستید، مشخص می‌کند. این توابع به شما کمک می‌کنند تا به سرعت و با انعطاف‌پذیری بیشتری به اطلاعات وابستگی دسترسی پیدا کنید.

سناریوهای پیشرفته و ملاحظات مهم
وابستگی‌های بین پایگاه داده‌ها (Cross-Database Dependencies): sys.sql_expression_dependencies به خوبی می‌تواند وابستگی‌ها را حتی در پایگاه‌های داده دیگر روی همان سرور شناسایی کند، به شرطی که نام پایگاه داده به وضوح در تعریف شیء ذکر شده باشد. ستون‌های referenced_server_name و referenced_database_name این اطلاعات را فراهم می‌کنند.
وابستگی‌های Schema-Bound و Non-Schema-Bound: اشیای SCHEMA-BOUND (مانند Viewهایی که با WITH SCHEMABINDING ایجاد شده‌اند) وابستگی‌های قوی‌تری دارند و تغییرات در اشیای مرجع آن‌ها با خطا مواجه می‌شود تا سازگاری حفظ شود. sys.sql_expression_dependencies هر دو نوع را ردیابی می‌کند.
وابستگی‌های Triggerها: Triggerها نیز نوعی Stored Procedure هستند و وابستگی‌های آن‌ها نیز توسط همین ویوها قابل ردیابی است.
ملاحظات و محدودیت‌ها:
Delayed Name Resolution (وضوح نام با تأخیر): اگر یک Stored Procedure به یک شیء اشاره کند که در زمان کامپایل وجود نداشته و بعداً ایجاد شود، sys.sql_expression_dependencies ممکن است نتواند آن وابستگی را ردیابی کند تا زمانی که شیء مربوطه دوباره کامپایل شود.
Dynamic SQL (کوئری‌های پویا): وابستگی‌هایی که در داخل رشته‌های SQL پویا (با استفاده از EXEC یا sp_executesql) ایجاد می‌شوند، قابل ردیابی توسط این کاتالوگ ویوها نیستند، زیرا SQL Server در زمان کامپایل نمی‌تواند آن‌ها را تحلیل کند.
Encryption (رمزگذاری): اشیای رمزگذاری شده (مانند Stored Procedureهای ایجاد شده با WITH ENCRYPTION) نیز به دلیل عدم دسترسی به متن اصلی، وابستگی‌هایشان قابل ردیابی نیست.

نتیجه‌گیری

با استفاده از sys.sql_expression_dependencies و توابع sys.dm_sql_referenced_entities و sys.dm_sql_referencing_entities، متخصصین SQL Server می‌توانند به طور مؤثر و دقیق وابستگی‌های اشیاء پایگاه داده را شناسایی کنند. این دانش نه تنها برای مدیریت روزانه و اشکال‌زدایی ضروری است، بلکه در برنامه‌ریزی برای تغییرات ساختاری و بهینه‌سازی عملکرد نیز نقش کلیدی دارد. با درک محدودیت‌ها و استفاده صحیح از این ابزارها، می‌توانید از یک پایگاه داده پایدارتر و قابل نگهداری‌تر اطمینان حاصل کنید.