اسکریپت لیست جداول با بیشترین تعداد Foreign Key

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

در پایگاه داده SQL Server، کلید خارجی یا Foreign Key یکی از مهم‌ترین ابزارها برای ایجاد ارتباط میان جداول و حفظ یکپارچگی اطلاعات است.
با استفاده از کلید خارجی می‌توان مشخص کرد که مقادیر یک ستون یا مجموعه‌ای از ستون‌ها باید با مقادیر معتبر موجود در یک کلید اصلی یا کلید یکتای جدول دیگر مطابقت داشته باشند.
در پروژه‌های بزرگ، ممکن است تعداد زیادی جدول با یکدیگر ارتباط داشته باشند.
چنین شرایطی، شناسایی جدول‌هایی که بیشترین تعداد Foreign Key را دارند، می‌تواند برای تحلیل ساختار پایگاه داده، بررسی وابستگی‌ها، مدیریت تغییرات و بهینه‌سازی عملیات نگهداری بسیار مفید باشد.
برای انجام این کار در SQL Server می‌توان از اسکریپت‌های T-SQL استفاده کرد.
این اسکریپت‌ها اطلاعات مربوط به کلیدهای خارجی را از نماهای سیستمی (System Catalog Views) استخراج می‌کنند و تعداد ارتباطات هر جدول را محاسبه می‌کنند.

منظور از جدول با بیشترین تعداد Foreign Key چیست؟

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

 

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

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

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

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

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

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

اسکریپت شمارش Foreign Keyهای هر جدول

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

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    COUNT(fk.object_id) AS ForeignKeyCount
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
LEFT JOIN sys.foreign_keys AS fk
    ON fk.parent_object_id = t.object_id
GROUP BY
    s.name,
    t.name
ORDER BY
    ForeignKeyCount DESC,
    SchemaName,
    TableName;

توضیح اسکریپت

در بخش اول، از sys.tables برای دریافت فهرست جدول‌های کاربر استفاده می‌شود.
این نما اطلاعات جدول‌هایی را ارائه می‌کند که در پایگاه داده جاری تعریف شده‌اند.
سپس با استفاده از sys.schemas، نام Schema هر جدول استخراج می‌شود.
نمایش نام Schema اهمیت دارد؛ زیرا ممکن است دو جدول با نام یکسان در Schemaهای متفاوت وجود داشته باشند.
در مرحله بعد، نمای sys.foreign_keys به جدول‌ها متصل می‌شود.
ستون parent_object_id مشخص می‌کند که هر محدودیت Foreign Key در کدام جدول تعریف شده است.
استفاده از LEFT JOIN باعث می‌شود جدول‌هایی که هیچ Foreign Key ندارند نیز در نتیجه باقی بمانند.
برای این جدول‌ها مقدار ForeignKeyCount برابر صفر خواهد بود.
تابع COUNT تعداد محدودیت‌های خارجی هر جدول را محاسبه می‌کند و دستور GROUP BY نتایج را بر اساس نام Schema و جدول گروه‌بندی می‌کند.
در پایان، دستور ORDER BY ForeignKeyCount DESC جدول‌هایی را که بیشترین تعداد کلید خارجی دارند، در ابتدای خروجی قرار می‌دهد.

نمایش فقط جدول‌های دارای بیشترین تعداد Foreign Key

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

WITH ForeignKeyCounts AS
(
    SELECT
        s.name AS SchemaName,
        t.name AS TableName,
        COUNT(fk.object_id) AS ForeignKeyCount
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
        ON t.schema_id = s.schema_id
    LEFT JOIN sys.foreign_keys AS fk
        ON fk.parent_object_id = t.object_id
    GROUP BY
        s.name,
        t.name
)
SELECT
    SchemaName,
    TableName,
    ForeignKeyCount
FROM ForeignKeyCounts
WHERE ForeignKeyCount =
(
    SELECT MAX(ForeignKeyCount)
    FROM ForeignKeyCounts
)
ORDER BY
    SchemaName,
    TableName;

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

نمایش جدول‌هایی که بیشترین ارجاع خارجی را دریافت می‌کنند

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

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    COUNT(fk.object_id) AS ReferencingForeignKeyCount
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
LEFT JOIN sys.foreign_keys AS fk
    ON fk.referenced_object_id = t.object_id
GROUP BY
    s.name,
    t.name
ORDER BY
    ReferencingForeignKeyCount DESC,
    SchemaName,
    TableName;

در این مثال، تفاوت اصلی در شرط اتصال است:

fk.referenced_object_id = t.object_id

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

نکات مهم هنگام استفاده از اسکریپت

هنگام تحلیل ساختار پایگاه داده باید چند نکته را در نظر بگیرید.

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

USE YourDatabaseName;
GO

نام YourDatabaseName را با نام واقعی پایگاه داده جایگزین کنید.

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

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

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