اسکریپت لیست تمامی Check Constraint های تعریف شده در یک دیتابیس

اسکریپت لیست تمامی Check Constraint های تعریف شده در یک دیتابیس

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

ALTER TABLE Products
ADD CONSTRAINT CK_Products_Price
CHECK (Price > 0);

پس از ایجاد Constraint، ممکن است در یک دیتابیس بزرگ تعداد زیادی CHECK Constraint داشته باشیم.
در چنین شرایطی مشاهده و بررسی آن‌ها از طریق رابط گرافیکی SQL Server Management Studio همیشه سریع‌ترین روش نیست.
به همین دلیل می‌توانیم با استفاده از یک Query، تمام Check Constraintهای موجود در دیتابیس را به همراه اطلاعاتی مانند نام Constraint، نام جدول، نام Schema و عبارت شرطی آن‌ها استخراج کنیم.

 

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

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

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

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

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

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

استفاده از sys.check_constraints

در SQL Server اطلاعات مربوط به Check Constraintها در Catalog Viewهای سیستمی ذخیره می‌شود.
یکی از مهم‌ترین Viewها برای این کار، sys.check_constraints است.
یک Query ساده برای مشاهده Check Constraintهای دیتابیس به شکل زیر است:

SELECT
    name AS ConstraintName,
    object_id,
    parent_object_id,
    definition
FROM sys.check_constraints
ORDER BY name;

ستون name نام Constraint را نمایش می‌دهد.
ستون object_id شناسه داخلی Constraint و ستون parent_object_id شناسه آبجکتی است که Constraint به آن تعلق دارد.
همچنین ستون definition شرط تعریف‌شده برای Constraint را نشان می‌دهد.
برای مثال، ممکن است خروجی چیزی شبیه این باشد:

ConstraintName          Definition
---------------------   ----------------
CK_Product_Price        ([Price]>(0))
CK_Product_Stock        ([Stock]>=(0))
CK_User_Age             ([Age]>=(18))

اما در بسیاری از مواقع فقط نام Constraint و Definition کافی نیست.
معمولاً هنگام بررسی ساختار دیتابیس، لازم است بدانیم هر Constraint دقیقاً روی کدام جدول و کدام Schema قرار گرفته است.

نمایش نام Schema و Table

برای دریافت اطلاعات کامل‌تر می‌توانیم sys.check_constraints را با sys.tables و sys.schemas Join کنیم:

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    cc.name AS ConstraintName,
    cc.definition AS CheckDefinition
FROM sys.check_constraints AS cc
INNER JOIN sys.tables AS t
    ON cc.parent_object_id = t.object_id
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
ORDER BY
    s.name,
    t.name,
    cc.name;

این Query یکی از کاربردی‌ترین اسکریپت‌ها برای مستندسازی Constraintهای دیتابیس است.
در اینجا parent_object_id مشخص می‌کند که Constraint متعلق به کدام جدول است.
سپس با اتصال جدول sys.tables به sys.schemas می‌توانیم نام واقعی جدول و Schema را به دست بیاوریم.
خروجی می‌تواند به شکل زیر باشد:

SchemaName   TableName    ConstraintName       CheckDefinition
-----------  -----------  -------------------  --------------------
dbo          Products     CK_Product_Price     ([Price]>(0))
dbo          Products     CK_Product_Stock     ([Stock]>=(0))
dbo          Users        CK_User_Age          ([Age]>=(18))

نمایش Constraintهای فعال یا غیرفعال

در SQL Server یک Constraint می‌تواند در وضعیت فعال یا غیرفعال قرار داشته باشد.
برای بررسی وضعیت آن، می‌توانیم از ستون is_disabled استفاده کنیم:

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    cc.name AS ConstraintName,
    cc.is_disabled AS IsDisabled,
    cc.definition AS CheckDefinition
FROM sys.check_constraints AS cc
INNER JOIN sys.tables AS t
    ON cc.parent_object_id = t.object_id
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
ORDER BY
    s.name,
    t.name,
    cc.name;

اگر مقدار IsDisabled برابر 0 باشد، Constraint فعال است و اگر مقدار آن 1 باشد، Constraint غیرفعال است.
البته هنگام تحلیل وضعیت Constraintها، فقط فعال یا غیرفعال بودن آن‌ها اهمیت ندارد.
بعضی Constraintها ممکن است فعال باشند اما داده‌های موجود در جدول هنگام ایجاد یا فعال‌سازی Constraint به‌درستی بررسی نشده باشند.
به همین دلیل در پروژه‌های حساس بهتر است وضعیت is_not_trusted نیز بررسی شود.

بررسی Trusted بودن Constraint

SQL Server اطلاعاتی درباره Trusted بودن Check Constraint نیز در اختیار ما قرار می‌دهد.
برای مشاهده این وضعیت می‌توانیم Query زیر را اجرا کنیم:

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    cc.name AS ConstraintName,
    cc.is_disabled AS IsDisabled,
    cc.is_not_trusted AS IsNotTrusted,
    cc.definition AS CheckDefinition
FROM sys.check_constraints AS cc
INNER JOIN sys.tables AS t
    ON cc.parent_object_id = t.object_id
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
ORDER BY
    s.name,
    t.name,
    cc.name;

 

ستون is_not_trusted می‌تواند هنگام بررسی و نگهداری دیتابیس اهمیت زیادی داشته باشد.
در حالت عادی انتظار داریم Constraint معتبر و مورد اعتماد SQL Server باشد.
اگر مقدار این ستون 1 باشد، باید علت آن بررسی شود.

نمایش Constraintهای یک جدول خاص

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

SELECT
    cc.name AS ConstraintName,
    cc.definition AS CheckDefinition
FROM sys.check_constraints AS cc
WHERE cc.parent_object_id = OBJECT_ID('dbo.Products');

این روش برای عیب‌یابی بسیار مفید است. فرض کنید هنگام درج اطلاعات در جدول Products با خطای Constraint مواجه شده‌ایم. با اجرای این Query می‌توانیم تمام قوانین Check مربوط به جدول را مشاهده کنیم و متوجه شویم چه شرط‌هایی روی داده‌ها اعمال شده‌اند.

بهترین روش‌ها برای بررسی ساختار داخلی دیتابیس

استفاده از Catalog Viewهای SQL Server یکی از بهترین روش‌ها برای بررسی ساختار داخلی دیتابیس است.
Viewهایی مانند sys.check_constraints اطلاعات ارزشمندی درباره Check Constraintها در اختیار ما قرار می‌دهند و می‌توان با Join کردن آن‌ها با sys.tables و sys.schemas اطلاعات کامل‌تری مانند نام Schema، نام جدول و Definition هر Constraint را استخراج کرد.
اگر هدف، تهیه یک گزارش کامل از وضعیت Check Constraintهای دیتابیس باشد، Query زیر گزینه مناسبی است:

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    cc.name AS ConstraintName,
    cc.is_disabled AS IsDisabled,
    cc.is_not_trusted AS IsNotTrusted,
    cc.definition AS CheckDefinition
FROM sys.check_constraints AS cc
INNER JOIN sys.tables AS t
    ON cc.parent_object_id = t.object_id
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
ORDER BY
    s.name,
    t.name,
    cc.name;

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