اسکریپت لیست ستون‌های یک جدول به همراه Foreign Key‌ های آنها

اسکریپت لیست ستون‌های یک جدول به همراه Foreign Key‌ های آنها در SQL Server

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

 

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

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

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

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

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

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

 

کلید خارجی (Foreign Key) چیست؟

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

اسکریپت لیست ستون‌های جدول به همراه Foreign Key

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

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

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length AS MaxLength,
    c.is_nullable AS IsNullable,
    fk.name AS ForeignKeyName,
    rt.name AS ReferencedTable,
    rs.name AS ReferencedSchema,
    rc.name AS ReferencedColumn
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
INNER JOIN sys.columns AS c
    ON t.object_id = c.object_id
INNER JOIN sys.types AS ty
    ON c.user_type_id = ty.user_type_id
LEFT JOIN sys.foreign_key_columns AS fkc
    ON c.object_id = fkc.parent_object_id
    AND c.column_id = fkc.parent_column_id
LEFT JOIN sys.foreign_keys AS fk
    ON fkc.constraint_object_id = fk.object_id
LEFT JOIN sys.tables AS rt
    ON fkc.referenced_object_id = rt.object_id
LEFT JOIN sys.schemas AS rs
    ON rt.schema_id = rs.schema_id
LEFT JOIN sys.columns AS rc
    ON fkc.referenced_object_id = rc.object_id
    AND fkc.referenced_column_id = rc.column_id
WHERE
    s.name = @SchemaName
    AND t.name = @TableName
ORDER BY
    c.column_id;

در این اسکریپت، نام اسکیمای جدول و نام جدول از طریق متغیرها مشخص می‌شوند.
در مثال بالا، اطلاعات جدول Orders در اسکیمای dbo استخراج خواهد شد.
کافی است مقدار @TableName را تغییر دهید تا اطلاعات جدول موردنظر نمایش داده شود.

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

۱. استفاده از sys.tables

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

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

در SQL Server، جدول‌ها در اسکیمای مشخصی قرار می‌گیرند.
اسکیمای dbo یکی از اسکیمای رایج است، اما ممکن است در یک پایگاه داده چندین اسکیمای مختلف وجود داشته باشد.
استفاده از sys.schemas کمک می‌کند جدول را با نام اسکیمای صحیح شناسایی کنیم.

۳. استفاده از sys.columns

نمای sys.columns اطلاعات ستون‌های هر جدول را ارائه می‌کند.
از طریق این نما می‌توان نام ستون، شماره آن در جدول و ویژگی‌هایی مانند پذیرش مقدار NULL را دریافت کرد.
ستون column_id ترتیب ستون‌ها را مشخص می‌کند.
به همین دلیل، در انتهای اسکریپت از ORDER BY c.column_id استفاده شده است تا نتیجه با ترتیب تعریف ستون‌ها در جدول نمایش داده شود.

۴. استفاده از sys.types

برای شناسایی نوع داده هر ستون، از نمای sys.types استفاده می‌کنیم.
این اطلاعات مشخص می‌کند ستون از نوع‌هایی مانند int، nvarchar، date یا decimal است.
ستون max_length نیز اندازه تعریف‌شده ستون را بر اساس بایت نشان می‌دهد.
توجه داشته باشید که برای برخی انواع داده، مانند nvarchar، این مقدار تعداد بایت است و مستقیماً تعداد کاراکترها را نشان نمی‌دهد.

۵. استفاده از sys.foreign_key_columns

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

۶. شناسایی جدول و ستون مرجع

با استفاده از referenced_object_id و referenced_column_id می‌توان جدول و ستونی را که کلید خارجی به آن اشاره می‌کند شناسایی کرد.
در اسکریپت، این اطلاعات با اتصال به sys.tables، sys.schemas و sys.columns استخراج می‌شوند.
برای مثال، اگر ستون CustomerID در جدول Orders به ستون CustomerID در جدول Customers متصل باشد، نتیجه می‌تواند نام ستون فعلی، نام کلید خارجی، نام جدول مرجع و نام ستون مرجع را نمایش دهد.

چرا در اسکریپت از LEFT JOIN استفاده شده است؟

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

کاربردهای اسکریپت لیست ستون‌های یک جدول به همراه Foreign Key‌ های آنها

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

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

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