一个列出表外键依赖层级的脚本

发布时间：2020-05-23 04:30:33 所属栏目：程序设计来源：互联网

导读：来源： http://sqlblog.com/blogs/jamie_t

来源： http://sqlblog.com/blogs/jamie_thomson/archive/2009/09/08/deriving-a-list-of-tables-in-dependency-order.aspx

SET

NOCOUNT ON

;

DECLARE

@RITable

TABLE

(

object_id INT PRIMARY

KEY

SchemaName SYSNAME NOT

NULL

TableName SYSNAME NOT

NULL

RILevel TINYINT DEFAULT

IsSelfReferencing TINYINT DEFAULT

HasExcludedRelationship TINYINT DEFAULT

UpdateCount TINYINT DEFAULT

);

INSERT

@RITable

(

object_id

SchemaName

TableName

RILevel

IsSelfReferencing

HasExcludedRelationship

UpdateCount

)

SELECT

tables .

object_id

schemas .

name

tables .

name

SUM ( CASE WHEN foreign_keys . parent_object_id IS NULL THEN 0 ELSE 1 END

)

SUM ( CASE WHEN foreign_keys02 . referenced_object_id IS NULL THEN 0 ELSE 1 END

)

FROM

sys.tables

tables

JOIN

sys.schemas schemas ON tables . schema_id = schemas .

schema_id

LEFT

JOIN sys.foreign_keys foreign_keys ON tables . object_id = foreign_keys .

parent_object_id

AND

tables . object_id = foreign_keys .

referenced_object_id

LEFT

JOIN sys.foreign_keys foreign_keys01 ON tables . object_id = foreign_keys01 .

parent_object_id

LEFT

JOIN sys.foreign_keys foreign_keys02 ON foreign_keys01 . parent_object_id = foreign_keys02 .

referenced_object_id

AND

foreign_keys01 . referenced_object_id = foreign_keys02 .

parent_object_id

AND

foreign_keys01 . parent_object_id <> foreign_keys01 .

referenced_object_id

WHERE

tables . name NOT IN ( 'sysdiagrams' , 'dtproperties'

)

GROUP

BY tables .

object_id

tables . name

;

DECLARE

@LookLevel INT

;

DECLARE

@MyRowcount INT ;

SELECT

@LookLevel =

@MyRowcount = - 1 ;

WHILE

( @MyRowcount <> 0

)

BEGIN

UPDATE

ChildTable

SET

RILevel = @LookLevel +

UpdateCount = ChildTable . UpdateCount +

FROM

@RITable ChildTable

JOIN

sys.foreign_keys foreign_keys ON ChildTable . object_id = foreign_keys .

parent_object_id

JOIN @RITable ParentTable ON foreign_keys . referenced_object_id = ParentTable .

object_id

AND

ParentTable . RILevel =

@LookLevel

LEFT

JOIN sys.foreign_keys foreign_keysEX ON foreign_keys . parent_object_id = foreign_keysEX .

referenced_object_id

AND

foreign_keys . referenced_object_id = foreign_keysEX .

parent_object_id

AND

foreign_keys . parent_object_id <> foreign_keys .

referenced_object_id

WHERE

ChildTable . object_id <> ParentTable .

object_id

AND foreign_keysEX . referenced_object_id IS NULL;

SELECT

@MyRowcount = @@ROWCOUNT ;

SELECT

@LookLevel = @LookLevel + 1

;

END

;

SELECT

RITable .

SchemaName SchemaName

RITable .

TableName TableName

RITable .

RILevel RILevel

CASE WHEN RITable . IsSelfReferencing >

THEN

CAST ( 1 AS BIT

)

ELSE

CAST ( 0 AS BIT

)

END

IsSelfReferencing

CASE WHEN RITable . HasExcludedRelationship >

THEN

CAST ( 1 AS BIT

)

ELSE

CAST ( 0 AS BIT

)

END

HasExcludedRelationship

FROM

@RITable RITable

ORDER

BY RITable . RILevel

DESC

RITable . TableName ASC

;

-- Excluded relationships

SELECT

foreign_keys01 . name

ForeignKeyName

ParentSchema . name

ParentSchema

ParentObject . name

ParentTable

ChildSchema . name

ChildSchema

ChildObject . name

ChildTable

FROM

sys.foreign_keys

foreign_keys01

JOIN

sys.foreign_keys foreign_keys02 ON foreign_keys01 . parent_object_id = foreign_keys02 .

referenced_object_id

AND

foreign_keys01 . referenced_object_id = foreign_keys02 .

parent_object_id

AND

foreign_keys01 . parent_object_id <> foreign_keys01 .

referenced_object_id

JOIN

sys.objects ParentObject ON foreign_keys01 . parent_object_id = ParentObject .

object_id

JOIN

sys.schemas ParentSchema ON ParentObject . schema_id = ParentSchema .

schema_id

JOIN

sys.objects ChildObject ON foreign_keys01 . referenced_object_id = ChildObject .

object_id

JOIN

sys.schemas ChildSchema ON ChildObject . schema_id = ChildSchema . schema_id

;

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!