Table Heirarchy

TableReferences.sql

declare @ChildTable varchar(100) = 'sc_TenantContactSource';
declare @ParentTable varchar(100);
declare @TableId int;

declare @Tables table
(
	Id int identity(1,1),
	ChildTable varchar(100),
	ParentTable varchar(100),
	Level int default (0),
	IsProcessed bit default (0)
)

-- Setup the base table
insert into @Tables
(
	ChildTable,
	ParentTable
)
select distinct
	t.name,
	t1.name
from
	sys.tables t
	left join sys.foreign_keys fk on t.object_id = fk.parent_object_id
	left join sys.tables t1 on fk.referenced_object_id = t1.object_id
where
	t.name = @ChildTable

--select * from @ReferencedTables
	
while exists (select 1 from @Tables where IsProcessed = 0)
begin
	select top 1 @TableId = Id, @ParentTable = ParentTable from @Tables where IsProcessed = 0 order by ParentTable;

	insert into @Tables
	(
		ChildTable,
		ParentTable,
		Level
	)
	select distinct
		t.name,
		t1.name,
		Level = (select Level + 1 from @Tables where Id = @TableId)
	from
		sys.tables t
		join sys.foreign_keys fk on t.object_id = fk.parent_object_id
		join sys.tables t1 on fk.referenced_object_id = t1.object_id
	where
		t.name = @ParentTable
		and t.name not in (select ChildTable from @Tables)

	update @Tables set IsProcessed = 1 where Id = @TableId;
end

select ChildTable, ParentTable, Level from @Tables order by Level