@H_301_0@

@H_301_0@

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

@H_301_0@SET

NOCOUNT ON @H_301_0@;

@H_301_0@DECLARE

@RITable @H_301_0@TABLE

@H_301_0@(

object_id INT PRIMARY @H_301_0@KEY

@H_301_0@,

SchemaName SYSNAME NOT @H_301_0@NULL

@H_301_0@,

TableName SYSNAME NOT @H_301_0@NULL

@H_301_0@,

RILevel TINYINT DEFAULT @H_301_0@ 0

@H_301_0@,

IsSelfReferencing TINYINT DEFAULT @H_301_0@ 0

@H_301_0@,

HasExcludedRelationship TINYINT DEFAULT @H_301_0@ 0

@H_301_0@,

UpdateCount TINYINT DEFAULT @H_301_0@ 0

@H_301_0@);

@H_301_0@INSERT

@H_301_0@ @RITable

@H_301_0@(

object_id @H_301_0@

@H_301_0@,

@H_301_0@SchemaName

@H_301_0@,

@H_301_0@TableName

@H_301_0@,

@H_301_0@RILevel

@H_301_0@,

@H_301_0@IsSelfReferencing

@H_301_0@,

@H_301_0@HasExcludedRelationship

@H_301_0@,

@H_301_0@UpdateCount

@H_301_0@)

@H_301_0@SELECT

tables . @H_301_0@object_id

@H_301_0@,

schemas . @H_301_0@name

@H_301_0@,

tables . @H_301_0@name

@H_301_0@,

@H_301_0@0

@H_301_0@,

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

@H_301_0@,

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

@H_301_0@,

@H_301_0@0

@H_301_0@FROM

sys.tables @H_301_0@ tables

@H_301_0@JOIN

sys.schemas schemas ON tables . schema_id = schemas . @H_301_0@schema_id

@H_301_0@LEFT

JOIN sys.foreign_keys foreign_keys ON tables . object_id = foreign_keys . @H_301_0@parent_object_id

@H_301_0@AND

tables . object_id = foreign_keys . @H_301_0@referenced_object_id

@H_301_0@LEFT

JOIN sys.foreign_keys foreign_keys01 ON tables . object_id = foreign_keys01 . @H_301_0@parent_object_id

@H_301_0@LEFT

JOIN sys.foreign_keys foreign_keys02 ON foreign_keys01 . parent_object_id = foreign_keys02 . @H_301_0@referenced_object_id

@H_301_0@AND

foreign_keys01 . referenced_object_id = foreign_keys02 . @H_301_0@parent_object_id

@H_301_0@AND

foreign_keys01 . parent_object_id <> foreign_keys01 . @H_301_0@referenced_object_id

@H_301_0@WHERE

tables . name NOT IN ( 'sysdiagrams' , 'dtproperties' @H_301_0@)

@H_301_0@GROUP

BY tables . @H_301_0@object_id

@H_301_0@,

tables . name @H_301_0@;

@H_301_0@DECLARE

@LookLevel INT @H_301_0@;

@H_301_0@DECLARE

@MyRowcount INT ; @H_301_0@

@H_301_0@SELECT

@LookLevel = @H_301_0@ 0

@H_301_0@,

@MyRowcount = - 1 ; @H_301_0@

@H_301_0@WHILE

( @MyRowcount <> 0 @H_301_0@)

@H_301_0@BEGIN

UPDATE @H_301_0@ ChildTable

@H_301_0@SET

RILevel = @LookLevel + @H_301_0@ 1

@H_301_0@,

UpdateCount = ChildTable . UpdateCount + @H_301_0@ 1

@H_301_0@FROM

@H_301_0@ @RITable ChildTable

@H_301_0@JOIN

sys.foreign_keys foreign_keys ON ChildTable . object_id = foreign_keys . @H_301_0@parent_object_id

@H_301_0@

JOIN @RITable ParentTable ON foreign_keys . referenced_object_id = ParentTable . @H_301_0@object_id

@H_301_0@AND

ParentTable . RILevel = @H_301_0@ @LookLevel

@H_301_0@LEFT

JOIN sys.foreign_keys foreign_keysEX ON foreign_keys . parent_object_id = foreign_keysEX . @H_301_0@referenced_object_id

@H_301_0@AND

foreign_keys . referenced_object_id = foreign_keysEX . @H_301_0@parent_object_id

@H_301_0@AND

foreign_keys . parent_object_id <> foreign_keys . @H_301_0@referenced_object_id

@H_301_0@WHERE

ChildTable . object_id <> ParentTable . @H_301_0@object_id

AND foreign_keysEX . referenced_object_id IS NULL; @H_301_0@

@H_301_0@SELECT

@MyRowcount = @@ROWCOUNT ; @H_301_0@

@H_301_0@SELECT

@LookLevel = @LookLevel + 1 @H_301_0@;

@H_301_0@END

; @H_301_0@

@H_301_0@SELECT

RITable . @H_301_0@SchemaName SchemaName

@H_301_0@,

RITable . @H_301_0@TableName TableName

@H_301_0@,

RITable . @H_301_0@RILevel RILevel

@H_301_0@,

CASE WHEN RITable . IsSelfReferencing > @H_301_0@ 0

@H_301_0@THEN

CAST ( 1 AS BIT @H_301_0@)

@H_301_0@ELSE

CAST ( 0 AS BIT @H_301_0@)

@H_301_0@END

@H_301_0@ IsSelfReferencing

@H_301_0@,

CASE WHEN RITable . HasExcludedRelationship > @H_301_0@ 0

@H_301_0@THEN

CAST ( 1 AS BIT @H_301_0@)

@H_301_0@ELSE

CAST ( 0 AS BIT @H_301_0@)

@H_301_0@END

@H_301_0@ HasExcludedRelationship

@H_301_0@FROM

@H_301_0@ @RITable RITable

@H_301_0@ORDER

BY RITable . RILevel @H_301_0@DESC

@H_301_0@,

RITable . TableName ASC @H_301_0@;

@H_301_0@-- Excluded relationships

@H_301_0@SELECT

foreign_keys01 . name @H_301_0@ ForeignKeyName

@H_301_0@,

ParentSchema . name @H_301_0@ ParentSchema

@H_301_0@,

ParentObject . name @H_301_0@ ParentTable

@H_301_0@,

ChildSchema . name @H_301_0@ ChildSchema

@H_301_0@,

ChildObject . name @H_301_0@ ChildTable

@H_301_0@FROM

sys.foreign_keys @H_301_0@ foreign_keys01

@H_301_0@JOIN

sys.foreign_keys foreign_keys02 ON foreign_keys01 . parent_object_id = foreign_keys02 . @H_301_0@referenced_object_id

@H_301_0@AND

foreign_keys01 . referenced_object_id = foreign_keys02 . @H_301_0@parent_object_id

@H_301_0@AND

foreign_keys01 . parent_object_id <> foreign_keys01 . @H_301_0@referenced_object_id

@H_301_0@JOIN

sys.objects ParentObject ON foreign_keys01 . parent_object_id = ParentObject . @H_301_0@object_id

@H_301_0@JOIN

sys.schemas ParentSchema ON ParentObject . schema_id = ParentSchema . @H_301_0@schema_id

@H_301_0@JOIN

sys.objects ChildObject ON foreign_keys01 . referenced_object_id = ChildObject . @H_301_0@object_id

@H_301_0@JOIN

sys.schemas ChildSchema ON ChildObject . schema_id = ChildSchema . schema_id @H_301_0@;

原文链接：https://www.f2er.com/javaschema/287757.html