اسکریپت لیست جداولی که توسط هیچ FK مورد ارجاع قرار نگرفته‌اند

اسکریپت لیست جداولی که توسط هیچ FK مورد ارجاع قرار نگرفته‌اند

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

Foreign Key چیست؟

Foreign Key یا کلید خارجی یکی از مهم‌ترین ابزارهای ایجاد ارتباط بین جداول در یک دیتابیس رابطه‌ای است.
فرض کنید دو جدول با نام‌های Customers و Orders داریم.
جدول Customers اطلاعات مشتریان را نگهداری می‌کند و جدول Orders سفارش‌های ثبت‌شده را ذخیره می‌کند.
ممکن است جدول سفارش‌ها دارای ستونی مانند CustomerId باشد که به ستون Id در جدول مشتریان اشاره می‌کند.
در این حالت می‌توان یک Foreign Key ایجاد کرد تا ارتباط بین دو جدول به صورت مشخص در ساختار دیتابیس ثبت شود.
برای مثال:

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerId)
REFERENCES Customers(Id);

در این مثال، جدول Orders به جدول Customers وابسته است و Customers جدول مرجع محسوب می‌شود.

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

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

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

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

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

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

چرا پیدا کردن جداول بدون FK مهم است؟

وجود نداشتن Foreign Key به این معنی نیست که یک جدول حتماً بدون استفاده است.
ممکن است یک جدول توسط برنامه، Stored Procedure، View یا حتی Queryهای مختلف استفاده شود، اما هیچ Foreign Key به آن وجود نداشته باشد.
با این حال، شناسایی چنین جداولی می‌تواند در تحلیل ساختار دیتابیس بسیار مفید باشد.
برای مثال هنگام نگهداری یک پروژه قدیمی ممکن است با ده‌ها یا صدها جدول مواجه شوید و ندانید کدام جدول‌ها در ساختار رابطه‌ای دیتابیس نقش اصلی دارند.
با پیدا کردن جداولی که هیچ FK به آن‌ها اشاره نمی‌کند، می‌توان بررسی ساختار دیتابیس را ساده‌تر کرد.
این کار در سناریوهای زیر کاربرد دارد:

• بررسی دیتابیس‌های قدیمی

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

• پیدا کردن جداول مرجع

• تحلیل وابستگی بین جداول

• بررسی جداول احتمالی بلااستفاده

• آماده‌سازی دیتابیس برای Migration

• پاک‌سازی ساختار دیتابیس

• تحلیل دیتابیس قبل از Refactoring
  
  
  
  
  

اسکریپت پیدا کردن جداولی که هیچ FK به آن‌ها اشاره نمی‌کند

در SQL Server اطلاعات مربوط به جداول و Foreign Keyها در System Catalog Views نگهداری می‌شود.
برای پیدا کردن جداولی که توسط هیچ Foreign Key مورد ارجاع قرار نگرفته‌اند، می‌توان از اسکریپت زیر استفاده کرد:

SELECT
    s.name AS SchemaName,
    t.name AS TableName
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
WHERE fk.object_id IS NULL
ORDER BY
    s.name,
    t.name;

این Query نام Schema و نام جدول‌هایی را نمایش می‌دهد که هیچ Foreign Key از جدول دیگری به آن‌ها اشاره نمی‌کند.

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

در بخش اول Query از sys.tables استفاده شده است:

FROM sys.tables AS t

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

INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id

به این ترتیب خروجی Query علاوه بر نام جدول، Schema مربوط به آن را نیز نمایش می‌دهد.

نقش sys.foreign_keys

قسمت مهم Query مربوط به View زیر است:

sys.foreign_keys

این System Catalog View اطلاعات مربوط به Constraintهای نوع Foreign Key را در SQL Server نگهداری می‌کند.
در Query از این بخش استفاده کرده‌ایم:

LEFT JOIN sys.foreign_keys AS fk
    ON fk.referenced_object_id = t.object_id

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

چرا از LEFT JOIN استفاده کرده‌ایم؟

استفاده از LEFT JOIN در این Query بسیار مهم است.
هدف ما فقط پیدا کردن جدول‌هایی نیست که FK دارند؛ بلکه می‌خواهیم جدول‌هایی را پیدا کنیم که هیچ FK به آن‌ها اشاره نمی‌کند.
به همین دلیل تمام جدول‌ها را از sys.tables دریافت کرده و سپس اطلاعات Foreign Key را به آن‌ها متصل می‌کنیم.
اگر برای یک جدول هیچ Foreign Key مرتبطی وجود نداشته باشد، ستون‌های مربوط به fk مقدار NULL خواهند داشت.
در نهایت با شرط زیر فقط همین جدول‌ها را انتخاب می‌کنیم:

WHERE fk.object_id IS NULL

در نتیجه خروجی، جدول‌هایی هستند که هیچ Foreign Key آن‌ها را به عنوان جدول مرجع استفاده نکرده است.

تفاوت جدول بدون FK با جدول بدون استفاده

نکته بسیار مهمی وجود دارد که نباید هنگام تحلیل نتیجه Query فراموش کنیم.
اگر یک جدول در خروجی این اسکریپت قرار گرفت، نمی‌توان نتیجه گرفت که جدول بلااستفاده است.
برای مثال ممکن است جدول زیر هیچ Foreign Key ورودی نداشته باشد:

Products

اما برنامه از آن برای نمایش محصولات استفاده کند.
همچنین ممکن است Stored Procedure زیر مستقیماً اطلاعات آن را بخواند:

SELECT *
FROM Products;

یا یک View از این جدول استفاده کند.
بنابراین خروجی این اسکریپت فقط نشان می‌دهد که هیچ Foreign Key به جدول موردنظر اشاره نمی‌کند؛ نه اینکه جدول در سیستم استفاده نمی‌شود.

بررسی جدول‌های بدون FK ورودی و خروجی

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

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName
FROM sys.tables AS t
LEFT JOIN sys.foreign_keys AS fk
    ON fk.referenced_object_id = t.object_id
WHERE fk.object_id IS NULL
ORDER BY
    SchemaName,
    TableName;

استفاده از SCHEMA_NAME نیز باعث می‌شود نیازی به Join جداگانه با sys.schemas نداشته باشیم.

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

در بسیاری از پروژه‌ها بهتر است فقط User Tableها بررسی شوند.
sys.tables به صورت معمول جداول کاربری را برمی‌گرداند و برای این سناریو گزینه مناسبی است.
اگر دیتابیس شما بسیار بزرگ باشد، می‌توانید خروجی را با اطلاعات دیگری نیز ترکیب کنید؛ برای مثال تعداد رکوردهای هر جدول، تاریخ ایجاد جدول یا سایر مشخصات ساختاری.

کاربرد این اسکریپت در پروژه‌های واقعی

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

Users
Products
Categories
Orders
OrderDetails
Payments
Logs
Settings

ممکن است روابط زیر وجود داشته باشند:

Orders → Users
OrderDetails → Orders
OrderDetails → Products
Products → Categories
Payments → Orders

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

نکته مهم قبل از حذف جدول‌ها

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

• Stored Procedureها

• Viewها

• Functionها

• Triggerها

• Queryهای برنامه

• Entityها و Modelهای نرم‌افزار

• Jobهای SQL Server

• گزارش‌ها

• فرآیندهای ETL

• دسترسی‌ها و وابستگی‌های خارجی

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

جمع‌بندی

Foreign Key یکی از مهم‌ترین ابزارهای SQL Server برای ایجاد ارتباط و حفظ یکپارچگی بین جداول است.
با استفاده از System Catalog Viewهای SQL Server می‌توان وابستگی‌های بین جداول را بررسی کرد.
اسکریپت زیر یکی از ساده‌ترین روش‌ها برای پیدا کردن جدول‌هایی است که هیچ Foreign Key به آن‌ها اشاره نمی‌کند:

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName
FROM sys.tables AS t
LEFT JOIN sys.foreign_keys AS fk
    ON fk.referenced_object_id = t.object_id
WHERE fk.object_id IS NULL
ORDER BY
    SchemaName,
    TableName;

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