اسکریپت لیست جداول همراه با تعداد ارجاعات

اسکریپت لیست جداول همراه با تعداد ارجاعات (پر ارجاع‌ترین جداول)

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

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

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

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

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

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

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

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

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

اسکریپت استخراج تعداد ارجاعات جداول

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

SELECT
    SCHEMA_NAME(T.schema_id) AS SchemaName,
    T.name AS TableName,
    COUNT(FK.object_id) AS ReferenceCount
FROM sys.tables AS T
LEFT JOIN sys.foreign_keys AS FK
    ON FK.referenced_object_id = T.object_id
    AND FK.is_ms_shipped = 0
WHERE T.is_ms_shipped = 0
GROUP BY
    T.schema_id,
    T.name
ORDER BY
    ReferenceCount DESC,
    SchemaName,
    TableName;

این اسکریپت تمام جدول‌های کاربر در دیتابیس جاری را نمایش می‌دهد و آن‌ها را براساس تعداد ارجاعات مرتب می‌کند.
جدول‌هایی که بیشترین ارجاع را دارند، در ابتدای خروجی قرار می‌گیرند.
استفاده از LEFT JOIN اهمیت زیادی دارد؛ زیرا باعث می‌شود جدول‌هایی که هیچ کلید خارجی به آن‌ها اشاره نمی‌کند نیز در گزارش باقی بمانند. در چنین شرایطی، مقدار ReferenceCount برای این جدول‌ها برابر با صفر خواهد بود.

نحوه عملکرد اسکریپت لیست جداول همراه با تعداد ارجاعات

در عبارت sys.tables، اطلاعات مربوط به جدول‌های کاربر استخراج می‌شود.
سپس با استفاده از sys.foreign_keys، کلیدهای خارجی مرتبط با هر جدول شناسایی می‌شوند.
شرط زیر مشخص می‌کند که کلید خارجی باید به جدول مورد بررسی اشاره کند:

FK.referenced_object_id = T.object_id

ستون referenced_object_id شناسه جدولی را نگهداری می‌کند که کلید خارجی به آن ارجاع می‌دهد.
در مقابل، ستون parent_object_id شناسه جدولی است که کلید خارجی در آن تعریف شده است.
تابع COUNT(FK.object_id) تعداد کلیدهای خارجی اشاره‌کننده به هر جدول را محاسبه می‌کند.
با استفاده از GROUP BY، اطلاعات براساس اسکیمـا و نام جدول گروه‌بندی می‌شوند.
در پایان، عبارت ORDER BY ReferenceCount DESC نتیجه را از بیشترین تعداد ارجاعات به کمترین تعداد مرتب می‌کند.
به این ترتیب، شناسایی جدول‌های مرکزی دیتابیس آسان‌تر خواهد شد.

نمایش ده جدول پرارجاع

در دیتابیس‌هایی که تعداد جدول‌های زیادی دارند، ممکن است بخواهید فقط جدول‌های مهم‌تر را مشاهده کنید.
برای این کار، می‌توانید از عبارت TOP (10) استفاده کنید.

SELECT TOP (10)
    SCHEMA_NAME(T.schema_id) AS SchemaName,
    T.name AS TableName,
    COUNT(FK.object_id) AS ReferenceCount
FROM sys.tables AS T
LEFT JOIN sys.foreign_keys AS FK
    ON FK.referenced_object_id = T.object_id
    AND FK.is_ms_shipped = 0
WHERE T.is_ms_shipped = 0
GROUP BY
    T.schema_id,
    T.name
ORDER BY
    ReferenceCount DESC,
    SchemaName,
    TableName;

این نسخه فقط ده جدول اول را نمایش می‌دهد و برای بررسی سریع ساختار دیتابیس کاربرد دارد.
اگر چند جدول تعداد ارجاعات یکسانی داشته باشند، ترتیب ثانویه براساس نام اسکیمـا و جدول مشخص می‌شود.
تفاوت تعداد ارجاعات و تعداد جدول‌های ارجاع‌دهنده
گاهی ممکن است یک جدول چند کلید خارجی داشته باشد که همگی از یک جدول دیگر به آن اشاره می‌کنند.
در این شرایط، تعداد کلیدهای خارجی با تعداد جدول‌های متمایز ارجاع‌دهنده متفاوت خواهد بود.
برای محاسبه تعداد جدول‌های متمایز، می‌توان از COUNT(DISTINCT FK.parent_object_id) استفاده کرد.
این عبارت هر جدول ارجاع‌دهنده را فقط یک بار محاسبه می‌کند.

SELECT
    SCHEMA_NAME(T.schema_id) AS SchemaName,
    T.name AS TableName,
    COUNT(DISTINCT FK.parent_object_id)
        AS ReferencingTableCount
FROM sys.tables AS T
LEFT JOIN sys.foreign_keys AS FK
    ON FK.referenced_object_id = T.object_id
    AND FK.is_ms_shipped = 0
WHERE T.is_ms_shipped = 0
GROUP BY
    T.schema_id,
    T.name
ORDER BY
    ReferencingTableCount DESC,
    SchemaName,
    TableName;

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

کاربرد جدول‌های پرارجاع در مدیریت دیتابیس

شناسایی جدول‌های پرارجاع هنگام تغییر ساختار پایگاه داده اهمیت زیادی دارد.
اگر یک جدول توسط چندین جدول دیگر مورد ارجاع قرار گرفته باشد، تغییر کلید اصلی یا حذف اطلاعات آن ممکن است بر روابط موجود تأثیر بگذارد.
برای مثال، حذف یک رکورد از جدول مشتریان ممکن است به دلیل وجود سفارش‌های وابسته با محدودیت کلید خارجی مواجه شود.
در چنین شرایطی، شناخت روابط موجود پیش از اجرای عملیات حذف، از بروز خطا جلوگیری می‌کند.
این گزارش همچنین برای مستندسازی دیتابیس، بررسی وابستگی‌ها و شناخت ساختار نرم‌افزار مفید است.
بااین‌حال، تعداد زیاد ارجاعات به‌تنهایی نشان‌دهنده کندبودن جدول نیست.
برای بررسی عملکرد باید عواملی مانند ایندکس‌ها، تعداد رکوردها و نحوه اجرای کوئری‌ها نیز در نظر گرفته شوند.

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

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