اسکریپت لیست جداول ارجاع‌کننده به یک جدول خاص (توسط FK)

اسکریپت لیست جداول ارجاع‌کننده به یک جدول خاص (توسط FK) در SQL Server

در طراحی پایگاه داده، ارتباط بین جداول یکی از مهم‌ترین بخش‌های ساختار اطلاعاتی محسوب می‌شود.
در SQL Server، کلید خارجی (Foreign Key) برای ایجاد ارتباط بین جداول و حفظ یکپارچگی داده‌ها استفاده می‌شود.
این کلید مشخص می‌کند که مقادیر یک ستون یا مجموعه‌ای از ستون‌ها در یک جدول، به مقادیر معتبر در جدول دیگری ارجاع می‌دهند.
گاهی هنگام مدیریت پایگاه داده لازم است بدانیم چه جدول‌هایی به یک جدول مشخص وابسته هستند.
برای مثال، ممکن است بخواهیم جدول Customers را بررسی کنیم و متوجه شویم کدام جدول‌ها از طریق کلید خارجی به آن ارجاع داده‌اند.
این اطلاعات هنگام تغییر ساختار جدول، حذف اطلاعات، بررسی وابستگی‌ها و بهینه‌سازی پایگاه داده اهمیت زیادی دارد.

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

منظور از جدول ارجاع‌کننده چیست؟

فرض کنید یک پایگاه داده فروشگاهی داریم که شامل جدول‌های زیر است:

• Customers برای ذخیره اطلاعات مشتریان

• Orders برای ذخیره سفارش‌ها

• Payments برای ذخیره پرداخت‌ها

• Addresses برای ذخیره نشانی مشتریان

• Products برای ذخیره محصولات

در این ساختار، جدول Customers ممکن است جدول اصلی باشد و جدول‌های دیگری مانند Orders و Addresses از طریق کلید خارجی به آن ارتباط داشته باشند.
برای مثال، جدول Orders می‌تواند ستونی با نام CustomerID داشته باشد که به ستون CustomerID در جدول Customers ارجاع می‌دهد.
در چنین شرایطی، Orders جدول ارجاع‌کننده و Customers جدول مرجع محسوب می‌شود.
شناسایی این ارتباط‌ها به ما کمک می‌کند وابستگی‌های یک جدول را بدون بررسی دستی تمام جدول‌های پایگاه داده پیدا کنیم.

اسکریپت لیست جداول ارجاع‌کننده به یک جدول خاص

در SQL Server، اطلاعات مربوط به کلیدهای خارجی در نماهای سیستمی مانند sys.foreign_keys و sys.tables ذخیره می‌شوند.
با استفاده از این اطلاعات می‌توان جدول‌هایی را که به یک جدول مشخص ارجاع می‌دهند، استخراج کرد.
کد زیر نام جدول موردنظر را دریافت می‌کند و فهرست جدول‌های ارجاع‌کننده به آن را نمایش می‌دهد.

DECLARE @TableName SYSNAME = N'Customers';

SELECT
    fk.name AS ForeignKeyName,
    SCHEMA_NAME(t_parent.schema_id) AS ReferencingSchema,
    t_parent.name AS ReferencingTable,
    c_parent.name AS ReferencingColumn,
    SCHEMA_NAME(t_ref.schema_id) AS ReferencedSchema,
    t_ref.name AS ReferencedTable,
    c_ref.name AS ReferencedColumn
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS t_parent
    ON fk.parent_object_id = t_parent.object_id
INNER JOIN sys.tables AS t_ref
    ON fk.referenced_object_id = t_ref.object_id
INNER JOIN sys.foreign_key_columns AS fkc
    ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns AS c_parent
    ON fkc.parent_object_id = c_parent.object_id
    AND fkc.parent_column_id = c_parent.column_id
INNER JOIN sys.columns AS c_ref
    ON fkc.referenced_object_id = c_ref.object_id
    AND fkc.referenced_column_id = c_ref.column_id
WHERE t_ref.name = @TableName
ORDER BY
    ReferencingSchema,
    ReferencingTable,
    ForeignKeyName;

در این مثال، مقدار Customers نام جدولی است که می‌خواهیم وابستگی‌های آن را بررسی کنیم.
کافی است این مقدار را با نام جدول موردنظر خود جایگزین کنید.
نکته مهم این است که اسکریپت بالا جدول‌هایی را پیدا می‌کند که از طریق کلید خارجی به جدول هدف ارجاع دارند؛ بنابراین، جدول‌هایی که صرفاً در کوئری‌ها یا کدهای برنامه‌نویسی استفاده شده‌اند، اما رابطه Foreign Key ندارند، در خروجی نمایش داده نمی‌شوند.
همچنین اگر جدول‌های هم‌نام در چند Schema مختلف وجود داشته باشند، بهتر است نام Schema را نیز در شرط جست‌وجو لحاظ کنید.

بررسی بخش‌های مختلف اسکریپت

برای درک بهتر نحوه عملکرد این کد، لازم است نقش اجزای اصلی آن را بشناسیم.

۱. تعیین نام جدول هدف

DECLARE @TableName SYSNAME = N'Customers';

در این قسمت، متغیری با نام @TableName تعریف می‌شود.
نوع داده SYSNAME در SQL Server معمولاً برای نگهداری نام اشیای پایگاه داده، مانند جدول‌ها و ستون‌ها، استفاده می‌شود.
با تغییر مقدار این متغیر می‌توان اسکریپت را برای جدول‌های دیگری نیز اجرا کرد؛ بدون اینکه لازم باشد بخش‌های دیگر کوئری را تغییر دهیم.

۲. استفاده از sys.foreign_keys

FROM sys.foreign_keys AS fk

نمای سیستمی sys.foreign_keys اطلاعات مربوط به محدودیت‌های کلید خارجی را نگهداری می‌کند.
نام هر کلید خارجی و شناسه جدول ارجاع‌کننده و جدول مرجع از جمله اطلاعاتی هستند که می‌توان از این نما دریافت کرد.
این بخش نقطه شروع مناسبی برای پیدا کردن روابط تعریف‌شده بین جدول‌ها است.

۳. تشخیص جدول ارجاع‌کننده و جدول مرجع

INNER JOIN sys.tables AS t_parent
    ON fk.parent_object_id = t_parent.object_id

در این رابطه، جدول t_parent نماینده جدولی است که کلید خارجی در آن تعریف شده است.
در مقابل، اتصال زیر جدول مرجع را مشخص می‌کند:

INNER JOIN sys.tables AS t_ref
    ON fk.referenced_object_id = t_ref.object_id

تفاوت این دو بخش بسیار مهم است. اگر هدف ما یافتن جدول‌هایی باشد که به یک جدول مشخص ارجاع می‌دهند، باید شناسه جدول مرجع را بررسی کنیم، نه فقط جدول دارای کلید خارجی را.

۴. استخراج نام ستون‌ها

نمای sys.foreign_key_columns اطلاعات مربوط به ستون‌های شرکت‌کننده در هر رابطه کلید خارجی را ارائه می‌دهد.
با اتصال این نما به sys.columns می‌توان نام ستون مبدأ و ستون مرجع را نیز استخراج کرد.
این قابلیت در روابط چندستونی اهمیت بیشتری دارد؛ زیرا یک کلید خارجی ممکن است از چند ستون تشکیل شده باشد.
البته در اسکریپت بالا، برای هر جفت ستون یک ردیف نمایش داده می‌شود.
در نتیجه، اگر کلید خارجی چندستونی باشد، نام همان کلید ممکن است در چند ردیف تکرار شود.

۵. فیلتر کردن جدول موردنظر

WHERE t_ref.name = @TableName

این شرط باعث می‌شود فقط روابطی نمایش داده شوند که جدول مرجع آن‌ها نام موردنظر را دارد.
برای محدود کردن جست‌وجو به یک Schema مشخص، می‌توان شرطی مانند زیر نیز اضافه کرد:

AND SCHEMA_NAME(t_ref.schema_id) = N'dbo'

این کار از ترکیب نتایج جدول‌های هم‌نام در Schemaهای مختلف جلوگیری می‌کند.

نکته مهم درباره Schema و جدول‌های هم‌نام

در SQL Server ممکن است چند جدول با نام یکسان، اما در Schemaهای متفاوت وجود داشته باشند؛ برای مثال:

- dbo.Customers

- Sales.Customers

اگر فقط نام جدول را در شرط WHERE بررسی کنیم، نتایج مربوط به هر دو جدول ممکن است نمایش داده شوند.
برای جلوگیری از این مشکل، بهتر است نام Schema و جدول را هم‌زمان مشخص کنیم:

DECLARE @TableName SYSNAME = N'Customers';
DECLARE @SchemaName SYSNAME = N'dbo';

SELECT
    SCHEMA_NAME(t_parent.schema_id) AS ReferencingSchema,
    t_parent.name AS ReferencingTable,
    fk.name AS ForeignKeyName
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS t_parent
    ON fk.parent_object_id = t_parent.object_id
INNER JOIN sys.tables AS t_ref
    ON fk.referenced_object_id = t_ref.object_id
WHERE t_ref.name = @TableName
  AND SCHEMA_NAME(t_ref.schema_id) = @SchemaName
ORDER BY
    ReferencingSchema,
    ReferencingTable;

این نسخه برای بررسی وابستگی‌های یک جدول مشخص در پایگاه داده‌های بزرگ، انتخاب دقیق‌تری است.

چه زمانی به اسکریپت لیست جداول ارجاع‌کننده به یک جدول خاص نیاز داریم؟

اسکریپت لیست جداول ارجاع‌کننده به یک جدول خاص در موقعیت‌های مختلفی کاربرد دارد:

• تغییر ساختار جدول

  پیش از تغییر ستون‌ها یا کلیدهای اصلی، می‌توان روابط وابسته را بررسی کرد.

• حذف جدول یا اطلاعات

  شناسایی روابط کلید خارجی به بررسی محدودیت‌های حذف کمک می‌کند.

• مهاجرت پایگاه داده

  هنگام انتقال اطلاعات به سرور جدید، شناخت وابستگی‌ها برای حفظ یکپارچگی داده‌ها ضروری است.

• مستندسازی دیتابیس

  می‌توان از خروجی اسکریپت برای تهیه فهرست روابط میان جدول‌ها استفاده کرد.

• عیب‌یابی خطاهای SQL

  هنگام مواجهه با خطاهای مربوط به Foreign Key، بررسی روابط موجود می‌تواند مسیر عیب‌یابی را روشن‌تر کند.