اسکریپت لیست جداول مورد ارجاع توسط Foreign Key در یک جدول

اسکریپت لیست جداول مورد ارجاع توسط Foreign Key در یک جدول

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

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

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

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

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

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

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

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

وقتی یک جدول دارای کلید اصلی یا کلید یکتای مشخصی باشد، جدول دیگری می‌تواند با تعریف Foreign Key به آن ارجاع دهد.
جدول ارجاع‌دهنده معمولاً اطلاعات وابسته را نگهداری می‌کند و جدول مرجع، اطلاعات اصلی را در اختیار دارد.
برای مثال، فرض کنید دو جدول زیر در پایگاه داده وجود دارند:

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

• جدول Orders برای ذخیره سفارش‌های ثبت‌شده

در جدول Orders ستونی با نام CustomerID وجود دارد که به ستون CustomerID در جدول Customers اشاره می‌کند.
در این مثال، جدول Orders به جدول Customers وابسته است.
بنابراین، اگر بخواهیم جدول‌های ارجاع‌دهنده به Customers را پیدا کنیم، باید جدول Orders را در نتایج مشاهده کنیم.
این موضوع در دیتابیس‌های بزرگ اهمیت بیشتری پیدا می‌کند؛ زیرا ممکن است ده‌ها جدول مختلف به یک جدول مشخص وابسته باشند و بررسی دستی آن‌ها زمان زیادی بگیرد.

اسکریپت SQL Server برای شناسایی جدول‌های ارجاع‌دهنده

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

DECLARE @ReferencedTable SYSNAME = N'Customers';

SELECT
    fk.name AS ForeignKeyName,
    SCHEMA_NAME(child.schema_id) AS ReferencingSchema,
    child.name AS ReferencingTable,
    SCHEMA_NAME(parent.schema_id) AS ReferencedSchema,
    parent.name AS ReferencedTable
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS child
    ON fk.parent_object_id = child.object_id
INNER JOIN sys.tables AS parent
    ON fk.referenced_object_id = parent.object_id
WHERE parent.name = @ReferencedTable
ORDER BY
    ReferencingSchema,
    ReferencingTable;

در این اسکریپت، مقدار متغیر @ReferencedTable نام جدولی را مشخص می‌کند که می‌خواهیم جدول‌های ارجاع‌دهنده به آن را پیدا کنیم.
در مثال بالا، این جدول Customers است.
بخش sys.foreign_keys اطلاعات مربوط به کلیدهای خارجی را ارائه می‌دهد.
اتصال آن به sys.tables باعث می‌شود نام جدول ارجاع‌دهنده و جدول مرجع در خروجی نمایش داده شود.
همچنین تابع SCHEMA_NAME نام شِمای هر جدول را مشخص می‌کند.
شرط WHERE فقط کلیدهای خارجی مربوط به جدول موردنظر را انتخاب می‌کند.
ر نهایت، دستور ORDER BY نتایج را بر اساس نام شِما و جدول مرتب می‌کند تا بررسی آن‌ها ساده‌تر شود.
نکته: این اسکریپت نام جدول را در تمام شِماهای دیتابیس بررسی می‌کند.
اگر چند شِما جدول‌هایی با نام یکسان داشته باشند، ممکن است نتایج مربوط به همه آن‌ها نمایش داده شود.
برای محدودکردن جست‌وجو به یک شِمای مشخص، بهتر است نام شِما نیز به‌عنوان شرط در نظر گرفته شود.

بررسی خروجی اسکریپت

فرض کنید در دیتابیس، سه جدول Orders، Payments و CustomerAddresses به جدول Customers ارجاع داده‌اند.
خروجی احتمالی اسکریپت می‌تواند به شکل زیر باشد:

ForeignKeyName                  ReferencingTable    ReferencedTable
FK_Orders_Customers                     Orders          Customers
FK_Payments_Customers                 Payments         Customers
FK_CustomerAddresses_Customers    CustomerAddresses    Customers

نام‌های نمایش‌داده‌شده صرفاً نمونه هستند و خروجی واقعی به ساختار پایگاه داده بستگی دارد.
ستون ForeignKeyName نام محدودیت کلید خارجی را نمایش می‌دهد.
ستون ReferencingTable جدول ارجاع‌دهنده و ستون ReferencedTable جدول مرجع را مشخص می‌کند.
این اطلاعات به توسعه‌دهنده کمک می‌کنند وابستگی‌های میان جدول‌ها را سریع‌تر شناسایی کند.

تفاوت جدول ارجاع‌دهنده و جدول مرجع

در زمان کار با Foreign Key، تشخیص جهت ارتباط اهمیت زیادی دارد.
جدول ارجاع‌دهنده جدولی است که کلید خارجی در آن تعریف شده است؛ در مقابل، جدول مرجع جدولی است که کلید خارجی به کلید اصلی یا یک کلید یکتای آن اشاره می‌کند.
برای مثال، اگر در جدول Orders ستونی به نام CustomerID به جدول Customers متصل باشد، جدول Orders ارجاع‌دهنده و جدول Customers مرجع است.
در ساختار داخلی SQL Server، مقدار parent_object_id در نمای sys.foreign_keys به جدول دارای کلید خارجی اشاره می‌کند؛ درحالی‌که referenced_object_id شناسه جدول مرجع را نگهداری می‌کند.
درک این تفاوت باعث می‌شود هنگام نوشتن کوئری‌های پیچیده‌تر، جهت ارتباط‌ها به‌اشتباه تفسیر نشود.

کاربردهای شناسایی جدول‌های وابسته

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

۱. جلوگیری از خطا هنگام حذف جدول

اگر جدولی توسط جدول‌های دیگر مورد ارجاع قرار گرفته باشد، حذف آن ممکن است به دلیل محدودیت‌های Foreign Key با خطا مواجه شود. شناسایی وابستگی‌ها پیش از حذف، امکان برنامه‌ریزی مناسب را فراهم می‌کند.

۲. بررسی تأثیر تغییر ساختار دیتابیس

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

۳. مستندسازی روابط دیتابیس

در پروژه‌های بزرگ، تهیه مستندات دقیق از روابط میان جدول‌ها اهمیت زیادی دارد.
استفاده از کوئری‌های سیستمی SQL Server می‌تواند بخش مهمی از این فرایند را خودکار کند.

۴. عیب‌یابی مشکلات داده‌ای

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

۵. تحلیل وابستگی‌ها هنگام انتقال اطلاعات

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

محدودکردن جست‌وجو به یک شِمای مشخص

اگر پایگاه داده دارای چند شِما باشد، بهتر است در پروژه‌های حرفه‌ای علاوه بر نام جدول، نام شِما نیز بررسی شود.
برای مثال، ممکن است دو جدول با نام Customers در شِماهای dbo و Sales وجود داشته باشند.
برای مشخص‌کردن جدول مرجع در شِمای dbo می‌توان شرط زیر را به اسکریپت اضافه کرد:

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

این شرط باید در بخش WHERE قرار گیرد تا فقط روابط مربوط به جدول Customers در شِمای dbo بررسی شوند.
همچنین اگر بخواهیم به‌جای نام جدول، شناسه داخلی آن را مبنا قرار دهیم، می‌توان از OBJECT_ID استفاده کرد.
این روش در برخی اسکریپت‌های مدیریتی که با اشیای مشخص دیتابیس سروکار دارند، کاربردی است؛ البته باید نام شِما نیز در صورت نیاز به‌درستی تعیین شود.