日期:2014-05-16 浏览次数:20474 次
;WITH FK --外键约束
AS
(
SELECT
SCH.name as foreign_schema_name, --外键schema名
FK.name as foreign_name, --外键名
FK.is_disabled , --是否禁用
FK.delete_referential_action_desc as delete_action,
FK.update_referential_action_desc as update_action,
FKC.constraint_column_id, --约束列的id
FKC.parent_object_id, --父对象的id
FKC.parent_column_id, --父对象列的id
FKC.referenced_object_id, --被引用的对象
FKC.referenced_column_id --被引用的对象中的列
FROM sys.foreign_keys FK
INNER JOIN sys.foreign_key_columns FKC
ON FK.object_id = FKC.constraint_object_id
INNER JOIN sys.schemas SCH
ON FK.schema_id = SCH.schema_id
),
TB --表和列
AS
(
SELECT
TB.object_id,
SCH.name as schema_name,
TB.name as table_name,
C.column_id as column_id,
C.name as column_name
FROM sys.tables TB WITH(NOLOCK)
INNER JOIN sys.columns C WITH(NOLOCK)
ON TB.object_id = C.object_id
INNER JOIN sys.schemas SCH WITH(NOLOCK)
ON TB.schema_id = SCH.schema_id
WHERE TB.is_ms_shipped = 0 -- 此条件表示仅查询不是由内部 SQL Server 组件创建对象
)
SELECT
FK.foreign_schema_name,
FK.foreign_name,
--FK.is_disabled,
--FK.delete_action,
--FK.update_action,
FK.constraint_column_id,
TBP.schema_name as parent_table_schema_name, --约束所属架构
TBP.table_name as '表', --约束所属表
TBP.column_name as '列' , --约束列
TBR.schema_name as referenced_table_schema_name, --被引用的列的架构
TBR.table_name as '被引用的表' , --被引用的对象的架构
TBR.column_name as '被引用的列' --被引用的对象的列
FROM FK
INNER JOIN TB TBP
ON FK.parent_object_id = TBP.object_id
AND FK.parent_column_id = TBP.column_id
INNER JOIN TB TBR
ON FK.referenced_object_id = TBR.object_id
AND FK.referenced_column_id = TBR.column_id
--where TBR.table_name = '表'
--and tbr.column_name = '列'