begin tran
--create schema test
create table test.test1(id int, ref int, constraint pk_test1 primary key (id))

create table test.test2(id int, ref int, constraint pk_test2 primary key (id))
alter table test.test2 add constraint fk_test2_test1 foreign key (id) references test.test1(id)

create table test.test3(id int, ref int, constraint pk_test3 primary key (id))
alter table test.test3 add constraint fk_test3_test2 foreign key (id) references test.test2(id)

create table test.test4(id int, ref int, constraint pk_test4 primary key (id))
alter table test.test4 add constraint fk_test4_test3 foreign key (id) references test.test3(id)

create table test.test5(id int, ref int, constraint pk_test5 primary key (id))
alter table test.test5 add constraint fk_test5_test4 foreign key (id) references test.test4(id)

create table test.test6(id int, ref int, constraint pk_test6 primary key (id))
alter table test.test6 add constraint fk_test6_test5 foreign key (id) references test.test5(id)

create table test.test7(id int, ref int, constraint pk_test7 primary key (id))
alter table test.test7 add constraint fk_test7_test6 foreign key (id) references test.test6(id)

create table test.test8(id int, ref int, constraint pk_test8 primary key (id))
alter table test.test8 add constraint fk_test8_test6 foreign key (id) references test.test6(id)

create table test.test9(id int, ref int, constraint pk_test9 primary key (id))
alter table test.test9 add constraint fk_test9_test2 foreign key (id) references test.test2(id)

SELECT DISTINCT fks.parent_object_id as ChildID, tbls1.[name] as ChildName, tbls.object_id as ParentID, tbls.[name] as ParentName
FROM sys.tables tbls
INNER JOIN sys.schemas schm ON schm.schema_id = tbls.schema_id
INNER JOIN sys.foreign_keys fks ON fks.referenced_object_id = tbls.object_id
INNER JOIN sys.tables tbls1 ON tbls1.object_id = fks.parent_object_id
WHERE schm.[name] = 'test'
order by tbls1.[name]

rollback tran

ChildID		ChildName	ParentID	ParentName	NestLevel
246343992	test2		214343878	test1		1
294344163	test3		246343992	test2		2
342344334	test4		294344163	test3		3
390344505	test5		342344334	test4		4
438344676	test6		390344505	test5		5
486344847	test7		438344676	test6		6
534345018	test8		438344676	test6		6
582345189	test9		246343992	test2		2

WITH FkNestLevels ( ChildID, ChildName, ParentID, ParentName,NestLevel ) 
AS 
( 
SELECT DISTINCT	  fks.parent_object_id	as ChildID
				, tbls1.[name]			as ChildName
				, tbls.object_id		as ParentID
				, tbls.[name]			as ParentName
				, 1						as NestLevel
FROM sys.tables tbls
INNER JOIN sys.schemas schm ON schm.schema_id = tbls.schema_id
INNER JOIN sys.foreign_keys fks ON fks.referenced_object_id = tbls.object_id
INNER JOIN sys.tables tbls1 ON tbls1.object_id = fks.parent_object_id
WHERE schm.[name] = 'test'
UNION ALL 
SELECT ChildID, ChildName, ParentID, ParentName, NestLevel+1
FROM sys.tables tbls
INNER JOIN sys.schemas schm ON schm.schema_id = tbls.schema_id
INNER JOIN sys.foreign_keys fks ON fks.referenced_object_id = tbls.object_id
INNER JOIN sys.tables tbls1 ON tbls1.object_id = fks.parent_object_id
INNER JOIN FkNestLevels nest ON nest.ChildID = nest.ParentID
WHERE schm.[name] = 'test'
)

194867811	test2	162867697	test1	1
242867982	test3	194867811	test2	1
290868153	test4	242867982	test3	1
338868324	test5	290868153	test4	1
386868495	test6	338868324	test5	1
434868666	test7	386868495	test6	1
482868837	test8	386868495	test6	1
530869008	test9	194867811	test2	1