مجموعه کامل اسکریپت‌های آماده SQL Server برای پروژه‌های واقعی

نوشته شده توسط: زهرا فرهنگی

تاریخ انتشار: ۲۳ آبان ۱۳۹۷

آخرین بروزرسانی: 23 فروردین 1404

زمان مطالعه: 5 دقیقه

دستور زیر تمامی Constraintهای تعریف شده روی جداول(و Vewها) شامل Primary Keys, Unique Key Constraintها و ایندکس‌ها, Foreign Keyها و Check و Default Constraintهای یک دیتابیس را نمایش خواهد داد.

select table_view,
object_type,
constraint_type,
constraint_name,
details
from (
select schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type],
case when c.[type] = 'PK' then 'Primary key'
when c.[type] = 'UQ' then 'Unique constraint'
when i.[type] = 1 then 'Unique clustered index'
when i.type = 2 then 'Unique index'
end as constraint_type,
isnull(c.[name], i.[name]) as constraint_name,
substring(column_names, 1, len(column_names)-1) as [details]
from sys.objects t
left outer join sys.indexes i
on t.object_id = i.object_id
left outer join sys.key_constraints c
on i.object_id = c.parent_object_id
and i.index_id = c.unique_index_id
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 = t.object_id
and ic.index_id = i.index_id
order by col.column_id
for xml path ('') ) D (column_names)
where is_unique = 1
and t.is_ms_shipped <> 1
union all
select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
'Table',
'Foreign key',
fk.name as fk_constraint_name,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name
from sys.foreign_keys fk
inner join sys.tables fk_tab
on fk_tab.object_id = fk.parent_object_id
inner join sys.tables pk_tab
on pk_tab.object_id = fk.referenced_object_id
inner join sys.foreign_key_columns fk_cols
on fk_cols.constraint_object_id = fk.object_id
union all
select schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Check constraint',
con.[name] as constraint_name,
con.[definition]
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
union all
select schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Default constraint',
con.[name],
col.[name] + ' = ' + con.[definition]
from sys.default_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) a
order by table_view, constraint_type, constraint_name

Table_View: نام جدول یا View به همراه نام Schema
Object_Type: نوع Object

– Table
– View

Constraint_Type: نوع Constraint

– Primary key
– Unique key
– Foregin key
– Check constraint
– Default constraint

Constraint_Name: نام Constraint یا ایندکس
Details: جزییات این Constraint

– Primary key – شامل اسامی ستون های شرکت کننده در PK
– Unique key – شامل اسامی ستون های شرکت کننده در UK
– Foregin key – نام جدول اصلی
– Check constraint – عبارت(فرمول) تعریف شده برای constraint
– Default constraint – نام ستون و مقدار/عبارت تعریف شده برای constrain

مشاهده کامل‌ترین و بروزترین آموزش sql server در نیک آموز

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

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

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

[select con.[name] as constraint_name,
schema_name(t.schema_id) + '.' + t.[name] as [table],
col.[name] as column_name,
con.[definition],
case when con.is_disabled = 0
then 'Active'
else 'Disabled'
end as [status]
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 con.name

Constraint_Name: نام Constraint
Table: نام Schema و جدولی که Constraint روی آن ایجاد شده است
Column_Name: نام ستون در مورد Check Constraint های تعریف شده در سطح ستون; برای Constraint های ایجادی در سطح جدول (table-level) مقدار null نمایش داده می‌شود.
Definition: عبارتی که برای این Check Constraint تعریف شده است
Status: وضعیت Constraint

o ‘Active’ در صورتی که Constraint فعال باشد.
o ‘Disabled’ برای Constraintهای غیرفعال

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

توسط دستور زیر می‌توان لیست تمامی Primary Key‌ها و Unique Key Constraintها و Unique Indexهای تعریف شده روی جداول و Viewهای یک دیتابیس را بدست آورد.

select schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type],
case when c.[type] = 'PK' then 'Primary key'
when c.[type] = 'UQ' then 'Unique constraint'
when i.[type] = 1 then 'Unique clustered index'
when i.type = 2 then 'Unique index'
end as constraint_type,
c.[name] as constraint_name,
substring(column_names, 1, len(column_names)-1) as [columns],
i.[name] as index_name,
case when i.[type] = 1 then 'Clustered index'
when i.type = 2 then 'Index'
end as index_type
from sys.objects t
left outer join sys.indexes i
on t.object_id = i.object_id
left outer join sys.key_constraints c
on i.object_id = c.parent_object_id
and i.index_id = c.unique_index_id
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 = t.object_id
and ic.index_id = i.index_id
order by col.column_id
for xml path ('') ) D (column_names)
where is_unique = 1
and t.is_ms_shipped <> 1
order by schema_name(t.schema_id) + '.' + t.[name]

Table_Vew: نام و Schema جدول و یا View
Object_Type: نوع Objectای که Index/constraint روی آن ایجاد شده است.

– Table
– View

Constraint_Type:

– Primary key: برای Primary Keyها
– Unique Constraint: برای Constraintهای ایجاد شده توسط دستور CONSTRAINT UNIQUE
– Unique Clustered Index :Unique Clustered Indexها بدون درنظر گرفتن Constraintهای از نوع Primary ویا Unique
– Unique Index :unique non-clustered indexها بدون درنظر گرفتن Constraintهای از نوع Primary ویا Unique

Constraint_Name :Constraintهای ایجاد شده بابت Primary و یا Unique Key, برای Unique Indexهای که مجزا از Constraint ایجاد شده باشند مقدار Null نمایش داده می‌شود.
Columns: اسامی ستون‌های شرکت کننده در ایندکس که با “,” از هم جدا شده‌‍اند.
Index_Name: نام ایندکس
Index_Type: نوع ایندکس

– Clustered Index- Clustered Index
– Index- Non-Clustered Index

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

دستور زیر تمامی Default 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: نام جدول به همراه نام شما
Column_id: شماره ستونِ جدول (این شماره در هر جدول Unique است)
Column_name: نام ستون
Definition :Expression تعریف شده برای آن فیلد به عنوان مقدار پیش فرض
Constraint_name: نام تعریف شده برای Conatraint مورد نظر

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

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

select 'No FKs >-' foreign_keys,
schema_name(fk_tab.schema_id) as schema_name,
fk_tab.name as table_name
from sys.tables fk_tab
left outer join sys.foreign_keys fk
on fk_tab.object_id = fk.referenced_object_id
where fk.object_id is null
order by schema_name(fk_tab.schema_id),
fk_tab.name

Foreign_Keys: نماد نشان دهنده عدم ارجاع از طریق FK
Schema_Name: نام schema جدول
Table_nName: نام جدول

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

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

select count(*) [table_count],
sum(case when fks.cnt + refs.cnt = 0 then 1 else 0 end)
as [loner_tables],
cast(cast(100.0 * sum(case when fks.cnt + refs.cnt = 0 then 1 else 0 end)
/ count(*) as decimal(36, 1)) as varchar) + '%' as [loner_ratio]
from (select schema_name(tab.schema_id) + '.' + tab.name as tab,
count(fk.name) cnt
from sys.tables as tab
left join sys.foreign_keys as fk
on tab.object_id = fk.parent_object_id
group by schema_name(tab.schema_id), tab.name) fks
inner join
(select schema_name(tab.schema_id) + '.' + tab.name as tab,
count(fk.name) cnt
from sys.tables as tab
left join sys.foreign_keys as fk
on tab.object_id = fk.referenced_object_id
group by schema_name(tab.schema_id), tab.name) refs
on fks.tab = refs.tab

Table_Count: تعداد جداول یک دیتابیس
Loner_Tables: تعداد Loner_Tablesهای دیتابیس
Loner_Ratio: نسبت جداول Loner به کل جداول

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

دستور زیر Default Constraintهای متفاوت تعریف شده روی ستون‌های جداول یک دیتابیس به همراه تعداد تکرار (وقوع) آنها را نمایش خواهد داد.

select
con.[definition] as default_definition,
count(distinct t.object_id) as [tables],
count(col.column_id) as [columns]
from sys.objects t
inner join sys.all_columns col
on col.object_id = t.object_id
left outer join sys.default_constraints con
on con.parent_object_id = t.object_id
and con.parent_column_id = col.column_id
where t.type = 'U'
group by con.[definition]
order by [columns] desc, [tables] desc

Default_Definition: مقدار/فرمول تعیین شده برای Default Constraint; برای ستون‎های فاقد این نوع Constraint مقدار Null نمایش داده خواهد شد.
Tables: تعداد جداول دارای این نوع Cconstraint (در مورد سطر Null در جواب دستور: تعداد جداولی که دارای ستونی فاقد Constraint هستند نمایش می‌دهد).
Columns: تعداد ستون‌هایی که دارای این نوع Constraint می‌باشند (برای مقدار Null: تعداد ستون‌های فاقد ژonstraint نمایش می‌دهد).