اسکریپت‌های بررسی Constraints در SQL Server به‌صورت کامل و آماده

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

تاریخ انتشار: ۲۳ مهر ۱۳۹۷

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

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

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

select schema_name(t.schema_id) as schema_name,
t.name as table_name,
t.create_date,
t.modify_date
from sys.tables t
order by schema_name,
table_name

Schema_name: نام schema
Table_name: نام جدول
Create_date: تاریخ ایجاد جدول
Modify_date: آخرین تاریخی که جدول با استفاده از دستور Alter تغییر یافته است

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

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

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

همانطور که می‌دانید SQL Server ستون‌هایی از انواع داده‌ای LOB را در Allocation Unitهای جداگانه به نام LOB_Data ذخیره می‌کند.
توسط کوئری زیر می‌توان میزان فضای تخصیص یافته به ازای داده‌های LOB را بدست آورد.

select case when spc.type in (1, 3) then 'Regular data'
else 'LOB data' end as allocation_type,
cast(sum(spc.used_pages * 8) / 1024.00 as numeric(36, 2)) as used_mb,
cast(sum(spc.total_pages * 8) / 1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables tab
inner join sys.indexes ind
on tab.object_id = ind.object_id
inner join sys.partitions part
on ind.object_id = part.object_id and ind.index_id = part.index_id
inner join sys.allocation_units spc
on part.partition_id = spc.container_id
group by case when spc.type in (1, 3) then 'Regular data'
else 'LOB data' end

Allocation_type: نوع فضای تخصیص یافته
LOB Data: فضای تخصیصی به ستون‌های داده ای LOB
Regular Data: فضای تخصیصی به سایر ستون‌ها
Used_mb: فضای استفاده شده (به مگابایت)
Allocated_mb: فضای تخصیص یافته (به مگابایت)

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

توسط دستور زیر می‌توان لیست ستون‌هایی از انواع داده‌ای LOB (Varchar(Max),Nvarchar(Max), Text, Ntext, Image, Varbinary(mMax), و Xml) را در یک دیتابیس بدست آورد.

select t.table_schema as schema_name,
t.table_name,
c.column_name,
c.data_type
from information_schema.columns c
inner join information_schema.tables t
on c.table_schema = t.table_schema
and c.table_name = t.table_name
where t.table_type = 'BASE TABLE'
and ((c.data_type in ('VARCHAR', 'NVARCHAR') and character_maximum_length = -1)
or c.data_type in ('TEXT', 'NTEXT', 'IMAGE', 'VARBINARY', 'XML', 'FILESTREAM'))
order by t.table_schema,
t.table_name,
c.column_name

Schema_name: نام شمای جدول
Table_name: نام جدول
Column_name: نام ستون
Data_type: نوع داده ای

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

دستور زیر اسامی ستون‌های جداول بین دو دیتابیس را مقایسه کرده و ستون‌هایی که در یکی از دو دیتابیس وجود نداشته باشد را لیست می‌کند.

select isnull(db1.table_name, db2.table_name) as [table],
isnull(db1.column_name, db2.column_name) as [column],
db1.column_name as database1,
db2.column_name as database2
from
(select schema_name(tab.schema_id) + ‘.’ + tab.name as table_name,
col.name as column_name
from [dataedo_6.0].sys.tables as tab
inner join [dataedo_6.0].sys.columns as col
on tab.object_id = col.object_id) db1
full outer join
(select schema_name(tab.schema_id) + ‘.’ + tab.name as table_name,
col.name as column_name
from [dataedo_7.0].sys.tables as tab
inner join [dataedo_7.0].sys.columns as col
on tab.object_id = col.object_id) db2
on db1.table_name = db2.table_name
and db1.column_name = db2.column_name
where (db1.column_name is null or db2.column_name is null)
order by 1, 2, 3

توضیح:

اسامی [dataedo_6.0] و [dataedo_7.0]را با اسامی دیتابیس‌های مورد نظر جایگزین کنید.

Table:

نام جدول به همراه نام شما

Column:

نام ستون

Database1:

اگر ستونی در جدولی از دیتابیس اول وجود داشته باشد , این ستون حاوی نام آن فیلد خواهد بود.

Database2:

اگر ستونی در جدولی از دیتابیس دوم وجود داشته باشد , این ستون حاوی نام آن فیلد خواهد بود.

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

به طور مثال در جدول dbo.columns ستون erd_nodes_columns در دیتابیس اول وجود دارد اما در دیتابیس دوم وجود ندارد.

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

select 'No FKs >-' refs,
fks.tab as [table],
'>- no FKs' fks
from
(select schema_name(tab.schema_id) + '.' + tab.name as tab,
count(fk.name) as fk_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) ref_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
where fks.fk_cnt + refs.ref_cnt = 0

Refs: نماد نشان دهنده عدم وجود Reference توسط جداول دیگر
Table: نام جدول همراه با نام شما
Fks: نماد نشان دهنده عدم وجود Foregin key در این جدول

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

توسط دستور زیر می‌توان لیست دیتابیس‌های موجود در یک Instance از SQL Server را بدست آورد

select [name] as database_name,
database_id,
create_date
from sys.databases
order by name

Database_Name: نام دیتابیس
Database_id :ID مربوط به دیتابیس. این شماره در هر Instance ,Unique می‌باشد
Created_Date: تاریخ ایجاد دیتابیس و یا تغییر نام داده شدن دیتابیس

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

توسط دستور زیر می‌توان لیست تمامی Schemaهای موجود در یک دیتابیس شامل شماهای پیش فرض و سیستمی (db_* ,sys,information_schema وguest) را بدست آورد.

select s.name as schema_name,
s.schema_id,
u.name as schema_owner
from sys.schemas s
inner join sys.sysusers u
on u.uid = s.principal_id
order by s.name

Schema_name: نام شِما
Schema_id :Id مربوط به شِما . این شماره در هر Instance , Unique می باشد
Schema_owner: نام شِمای مالک ؟؟؟؟؟

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

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

select s.name as schema_name,
s.schema_id,
u.name as schema_owner
from sys.schemas s
inner join sys.sysusers u
on u.uid = s.principal_id
where u.issqluser = 1
and u.name not in ('sys', 'guest', 'INFORMATION_SCHEMA')

Schema_name: نام شِما
Schema_id :Id مربوط به شِما. این شماره در هر Instance ,Unique می‌باشد.
Schema_owner: نام شِمای مالک ؟؟؟؟؟

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

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

select distinct
schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
'>-' as rel,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table
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
where pk_tab.[name] = 'Your table' -- enter table name here
-- and schema_name(pk_tab.schema_id) = 'Your table schema name'
order by schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name

توجه: نام جدول مورد نظر خود را درون کد جایگزاری کرده و در صورتی که چندین جدول همنام در Schemaهای مختلف وجود دارد می‌توانید با خارج کردن کد مربوطه از حالت کامنت, اطلاعات جدول مورد نظر را آسان‌تر بدست آورید.

Foreign_Table: نام جدول خارجی به همراه Schema
Rel: نماد ارتباط, مشخص کننده Foreign Key و جهت ارتباط
Primary_Table: نام جدول اصلی (مورد ارجاع) به همراه Schema; جدولی که به عنوان پارامتر داده شده است

لیست تمامی جداولی که توسط FK به جدول Production.Product از دیتابیس AdventureWorks ارجاع داده اند

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

select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
'>-' as rel,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
substring(column_names, 1, len(column_names)-1) as [fk_columns],
fk.name as fk_constraint_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
cross apply (select col.[name] + ', '
from sys.foreign_key_columns fk_c
inner join sys.columns col
on fk_c.parent_object_id = col.object_id
and fk_c.parent_column_id = col.column_id
where fk_c.parent_object_id = fk_tab.object_id
and fk_c.constraint_object_id = fk.object_id
order by col.column_id
for xml path ('') ) D (column_names)
order by schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name

Foreign_Table: نام جدول دارای FK به همراه نام Schema
Rel: نماد نشان دهنده ارتباط
Primary_Table: نام جدول اصلی (Referenced Table) همراه با نام Schema
FK_Columns: لیست اسامی ستون های FK
FK_Constraint_Name: نام Foreign Key Constraint

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

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

select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
'>-' as rel,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
fk_cols.constraint_column_id as no,
fk_col.name as fk_column_name,
' = ' as [join],
pk_col.name as pk_column_name,
fk.name as fk_constraint_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
inner join sys.columns fk_col
on fk_col.column_id = fk_cols.parent_column_id
and fk_col.object_id = fk_tab.object_id
inner join sys.columns pk_col
on pk_col.column_id = fk_cols.referenced_column_id
and pk_col.object_id = pk_tab.object_id
order by schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name,
fk_cols.constraint_column_id

Foreign_Table: نام جدول دارای FK به همراه نام schema
Rel: نماد نشان دهنده ارتباط
Primary_Table: نام جدول اصلی (referenced table)همراه با نام schema
no :ID مربوط به این ستون در Fk ایجاد شده . این شماره برای کلیدهایی که تنها شامل یک ستون باشند همواره برابر ۱ و برای کلیدهای ترکیبی به ازای هر ستون از کلید دارای مقادیر ۱ , ۲ و … خواهد بود
FK_Column_Name: نام ستون در جدول خارجی
Join: نماد نشان دهنده ارتباط بین هر جفت از ستون ها
Pk_Column_Name: نام ستون در جدول اصلی
FK_Constraint_Name: نام Foreign Key Constraint

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

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

select distinct
schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
'>-' as rel,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table
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
where fk_tab.[name] = 'Your table' -- enter table name here
-- and schema_name(fk_tab.schema_id) = 'Your table schema name'
order by schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name

توجه: نام جدول مورد نظر خود را درون کد جای‌گزاری کرده و در صورتی که چندین جدول همنام در Schemaهای مختلف وجود دارد می‌توانید با خارج کردن کد مربوطه از حالت کامنت, اطلاعات جدول مورد نظر را آسان تر بدست آورید.

Foreign_Table: نام جدول خارجی به همراه Schema; جدولی که به عنوان پارامتر داده شده است
Rel: نماد ارتباط, مشخص کننده Foreign Key و جهت ارتباط
Primary_Table: نام جدول اصلی (مورد ارجاع) به همراه Schema

یک نمونه از اجرای دستور بالا روی جدول Sales.SalesOrderHeader از دیتابیس Adventure Works

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

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
order by schema_name,
table_name,
column_id;