اسکریپت لیست تمامی Primary Key های یک دیتابیس

اسکریپت لیست تمامی Primary Key های یک دیتابیس در SQL Server

در مدیریت پایگاه‌های داده، شناخت ساختار جداول و نحوه شناسایی رکوردها یکی از مراحل مهم در توسعه، نگهداری و بهینه‌سازی سیستم‌های نرم‌افزاری است.
یکی از اطلاعاتی که معمولاً هنگام بررسی ساختار دیتابیس به آن نیاز داریم، فهرست کلیدهای اصلی یا Primary Keyها است.
این اطلاعات مشخص می‌کند هر جدول بر اساس چه ستون یا ستون‌هایی رکوردهای خود را به‌صورت یکتا شناسایی می‌کند.
در دیتابیس‌های کوچک، مشاهده این اطلاعات از طریق ابزارهایی مانند SQL Server Management Studio ساده است؛ اما در پروژه‌های بزرگ که تعداد زیادی جدول دارند، بررسی دستی تک‌تک جداول زمان‌بر خواهد بود.
در چنین شرایطی، استفاده از یک اسکریپت SQL می‌تواند تمام اطلاعات موردنیاز را در قالب یک گزارش منظم استخراج کند.

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

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

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

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

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

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

Primary Key چه نقشی در دیتابیس دارد؟

Primary Key محدودیتی است که یک یا چند ستون را به‌عنوان شناسه یکتای رکوردهای یک جدول تعیین می‌کند.
مقادیر این کلید نباید تکراری یا NULL باشند. برای مثال، در جدول کاربران، ستون UserID می‌تواند شناسه منحصربه‌فرد هر کاربر باشد.
در برخی جداول، یک ستون به‌تنهایی برای شناسایی رکوردها کافی نیست.
برای نمونه، جدول جزئیات سفارش ممکن است شامل ستون‌های OrderID و ProductID باشد و ترکیب این دو ستون به‌عنوان کلید اصلی تعریف شده باشد.
به این ساختار Composite Primary Key گفته می‌شود.
تشخیص این تفاوت هنگام استخراج اطلاعات اهمیت دارد؛ زیرا اگر اسکریپت تنها نام یک ستون را نمایش دهد، ممکن است بخشی از ساختار واقعی کلید اصلی نادیده گرفته شود.

اسکریپت استخراج تمام Primary Keyها

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

USE YourDatabaseName;
GO

سپس کوئری زیر را اجرا می‌کنیم:

SELECT
    DB_NAME() AS DatabaseName,
    s.name AS SchemaName,
    t.name AS TableName,
    kc.name AS PrimaryKeyName,
    c.name AS ColumnName,
    ic.key_ordinal AS KeyPosition
FROM sys.key_constraints AS kc
INNER JOIN sys.tables AS t
    ON t.object_id = kc.parent_object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
INNER JOIN sys.index_columns AS ic
    ON ic.object_id = t.object_id
    AND ic.index_id = kc.unique_index_id
INNER JOIN sys.columns AS c
    ON c.object_id = ic.object_id
    AND c.column_id = ic.column_id
WHERE kc.type = 'PK'
    AND ic.key_ordinal > 0
ORDER BY
    s.name,
    t.name,
    ic.key_ordinal;

این کوئری اطلاعات تمام کلیدهای اصلی جداول کاربر در دیتابیس انتخاب‌شده را نمایش می‌دهد.
ستون DatabaseName نام دیتابیس جاری را مشخص می‌کند و SchemaName برای شناسایی دقیق محل قرارگیری جدول استفاده می‌شود. TableName نام جدول و PrimaryKeyName نام محدودیت کلید اصلی را نشان می‌دهد.
ستون ColumnName نام هر یک از ستون‌های تشکیل‌دهنده کلید اصلی را مشخص می‌کند.
همچنین KeyPosition ترتیب ستون‌ها را در کلید اصلی نشان می‌دهد. وجود این ستون برای بررسی کلیدهای مرکب ضروری است.

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

بخش اصلی این اسکریپت از کاتالوگ‌های سیستمی SQL Server استفاده می‌کند.
نمای sys.key_constraints اطلاعات محدودیت‌های کلید اصلی و کلیدهای یکتا را در اختیار قرار می‌دهد.
با اعمال شرط kc.type = 'PK'، تنها محدودیت‌هایی انتخاب می‌شوند که از نوع Primary Key هستند.
نمای sys.tables اطلاعات جدول‌ها را فراهم می‌کند و sys.schemas نام Schema مربوط به هر جدول را مشخص می‌سازد.
این موضوع در دیتابیس‌هایی که از چند Schema استفاده می‌کنند اهمیت دارد؛ زیرا ممکن است نام جدول‌ها در Schemaهای مختلف یکسان باشد.
اتصال به sys.index_columns و sys.columns نیز مشخص می‌کند که هر کلید اصلی از کدام ستون‌ها تشکیل شده است.
در نهایت، مرتب‌سازی بر اساس Schema، نام جدول و ترتیب ستون‌ها باعث می‌شود گزارش خروجی خواناتر باشد.

نمونه خروجی و تفسیر آن

فرض کنید دیتابیس شامل جدول‌های Customers، Products و OrderDetails باشد.
خروجی ممکن است به شکل زیر نمایش داده شود:

TableName	PrimaryKeyName	ColumnName	KeyPosition
Customers	PK_Customers	CustomerID	1
Products	PK_Products	ProductID	1
OrderDetails	PK_OrderDetails	OrderID	1
OrderDetails	PK_OrderDetails	ProductID	2

در این مثال، دو جدول Customers و Products هرکدام یک ستون برای کلید اصلی دارند.
در مقابل، کلید اصلی جدول OrderDetails از دو ستون تشکیل شده است.
بنابراین، این جدول در خروجی دو ردیف خواهد داشت.
این رفتار طبیعی است و به معنای وجود دو کلید اصلی در جدول نیست.
هر جدول می‌تواند حداکثر یک محدودیت Primary Key داشته باشد، اما این محدودیت ممکن است چند ستون را در بر بگیرد.

نمایش هر Primary Key در یک ردیف

اگر هدف تهیه گزارش مدیریتی باشد، ممکن است بخواهیم نام تمام ستون‌های کلید اصلی هر جدول در یک ردیف نمایش داده شود.
در نسخه‌های SQL Server 2017 و جدیدتر، تابع STRING_AGG این کار را انجام می‌دهد.

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    kc.name AS PrimaryKeyName,
    STRING_AGG(
        CAST(QUOTENAME(c.name) AS NVARCHAR(MAX)),
        ', '
    ) WITHIN GROUP (
        ORDER BY ic.key_ordinal
    ) AS PrimaryKeyColumns
FROM sys.key_constraints AS kc
JOIN sys.tables AS t
    ON t.object_id = kc.parent_object_id
JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
JOIN sys.index_columns AS ic
    ON ic.object_id = t.object_id
    AND ic.index_id = kc.unique_index_id
JOIN sys.columns AS c
    ON c.object_id = t.object_id
    AND c.column_id = ic.column_id
WHERE kc.type = 'PK'
    AND ic.key_ordinal > 0
GROUP BY
    s.name,
    t.name,
    kc.name
ORDER BY
    s.name,
    t.name;

با استفاده از این روش، ستون‌های کلید اصلی مرکب در یک رشته کنار هم قرار می‌گیرند.
برای مثال، ستون‌های جدول OrderDetails به شکل [OrderID], [ProductID] نمایش داده می‌شوند.
این ساختار برای مستندسازی و تهیه گزارش‌های خلاصه مناسب است.

نکات فنی و محدودیت‌ها

این اسکریپت فقط اطلاعات دیتابیس انتخاب‌شده را استخراج می‌کند و برای مشاهده اطلاعات، کاربر باید مجوزهای لازم را داشته باشد.
همچنین جدول‌هایی که فاقد Primary Key هستند در خروجی ظاهر نمی‌شوند؛ زیرا کوئری بر اساس وجود محدودیت کلید اصلی فیلتر شده است.
نکته دیگر، تفاوت Primary Key با Unique Constraint است.
هر دو می‌توانند یکتایی مقادیر را تضمین کنند، اما از نظر ساختاری یکسان نیستند.
به همین دلیل، برای تهیه فهرست کلیدهای اصلی باید محدودیت‌های نوع PK بررسی شوند، نه تمام ایندکس‌های یکتا.
اگر هدف بررسی کامل طراحی دیتابیس باشد، بهتر است در کنار این گزارش، جدول‌های فاقد کلید اصلی نیز شناسایی شوند.
چنین گزارشی می‌تواند به تشخیص مشکلات طراحی و آماده‌سازی دیتابیس برای تغییرات بعدی کمک کند.

جمع‌بندی

استخراج تمامی Primary Keyهای یک دیتابیس با استفاده از کاتالوگ‌های سیستمی SQL Server روشی سریع و قابل‌اعتماد برای بررسی ساختار جداول است.
اسکریپت ارائه‌شده، نام دیتابیس، Schema، جدول، محدودیت کلید اصلی و ستون‌های مربوط به آن را استخراج می‌کند و ترتیب ستون‌ها را نیز حفظ می‌کند.
این گزارش در مستندسازی فنی، بررسی ساختار دیتابیس، تحلیل کلیدهای مرکب و برنامه‌ریزی برای مهاجرت داده‌ها کاربرد دارد.
انتخاب بین نمایش جداگانه ستون‌ها و تجمیع آن‌ها در یک ردیف نیز به هدف گزارش بستگی دارد؛ نسخه اول برای تحلیل دقیق‌تر و نسخه دوم برای مرور سریع ساختار دیتابیس مناسب‌تر است.