اسکریپت‌های کاربردی SQL برای مدیریت و کنترل کامل دیتابیس

توسط دستور زیر می‌توان لیست ستون‌های ضروری (Non Nullable) جداول یک دیتابیس را بدست آورد.

select schema_name(tab.schema_id) as schema_name,
tab.name as table_name,
col.column_id,
col.name as column_name,
t.name as data_type,
col.max_length,
col.precision
from sys.tables as tab
inner join sys.columns as col
on tab.object_id = col.object_id
left join sys.types as t
on col.user_type_id = t.user_type_id
where col.is_nullable = 0
order by schema_name,
table_name,
column_name;

Schema_name: نام شمای جدول
Table_name: نام جدول
Column_id: شماره ستون
Column_name: نام ستون
Data_type: نوع داده ای ستون
Max_length: حداکثر سایز Data Type
Precision: دقت Data Type

یک نمونه از اجرای کوئری بالا روی دیتابیس AdventureWorks

دستور زیر اسامی تمامی ستون‌های جداول یک دیتابیس به همراه اطلاعات Foreign Key تعریف شده روی آن ستون (در صورت وجود) را لیست خواهد کرد.

select schema_name(tab.schema_id) + '.' + tab.name as [table],
col.column_id,
col.name as column_name,
case when fk.object_id is not null then '>-' else null end as rel,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
pk_col.name as pk_column_name,
fk_cols.constraint_column_id as no,
fk.name as fk_constraint_name
from sys.tables tab
inner join sys.columns col
on col.object_id = tab.object_id
left outer join sys.foreign_key_columns fk_cols
on fk_cols.parent_object_id = tab.object_id
and fk_cols.parent_column_id = col.column_id
left outer join sys.foreign_keys fk
on fk.object_id = fk_cols.constraint_object_id
left outer join sys.tables pk_tab
on pk_tab.object_id = fk_cols.referenced_object_id
left outer join sys.columns pk_col
on pk_col.column_id = fk_cols.referenced_column_id
and pk_col.object_id = fk_cols.referenced_object_id
order by schema_name(tab.schema_id) + '.' + tab.name,
col.column_id

• Table: نام جدول به همراه نام schema
• Column_ID: شماره(id) ستون
• Column_Name: نام ستون
• Rel: نماد ارتباط , مشخص کننده Foreign Key و جهت ارتباط
• Primary_Table: جدول مرجع (اصلی)
• Pk_Column_Name: نام ستون ارجاع شده (عامل ارتباط) در جدول اصلی
• No: شماره (id) ستون در foreign key ایجاد شده
• Fk_Constraint_Name: نام constraint مربوط به این foreign key

نمونه‌ای از اجرای دستور بالا روی دیتابیس AdventureWorks

توسط دستور زیر می‌توان لیست تمامی Primary Key Constraint (PK)های یک دیتابیس را بدست آورد.

select schema_name(tab.schema_id) as [schema_name],
pk.[name] as pk_name,
substring(column_names, 1, len(column_names)-1) as [columns],
tab.[name] as table_name
from sys.tables tab
inner join sys.indexes pk
on tab.object_id = pk.object_id
and pk.is_primary_key = 1
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = tab.object_id
and ic.index_id = pk.index_id
order by col.column_id
for xml path ('') ) D (column_names)
order by schema_name(tab.schema_id),
pk.[name]

Schema_Name: نام Schema
Pk_Name: نامِ Constraint مربوط به PK
Columns: لیست اسامی ستون‌های تشکیل دهنده pk
Table_Nam: نام جدول

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

توسط دستور زیر می‌توان لیست تمامی Check Constraintهای تعریف شده روی یک جدول را بدست آورد.

select schema_name(t.schema_id) + '.' + t.[name] as [table],
col.column_id,
col.[name] as column_name,
con.[definition],
case when con.is_disabled = 0
then 'Active'
else 'Disabled'
end as [status],
con.[name] as constraint_name
from sys.check_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id
order by schema_name(t.schema_id) + '.' + t.[name],
col.column_id

•  Table: نام Schema و جدول
•  Column_ID :ID مربوط به آن ستون, این شماره در هر جدول یکتا می باشد
•  Column_Name: نام ستون در مورد Check Constraintهای تعریف شده در سطح ستون; برای constraint های ایجادی در سطح جدول (table-level) مقدار null نمایش داده می شود
•  Definition: عبارتی که برای این Check Constraint تعریف شده است
•  status: وضعیت Constraint
  o ‘Active’ در صورتی که Constraint فعال باشد
  o ‘Disabled’ برای Constraintهای غیرفعال
•  Constraint_Name: نام Constraint

یک نمونه از اجرای دستور فوق روی دیتابیس AdventureWords