Created
March 1, 2018 22:17
-
-
Save JoeGannon/8846534823487aed27cd934264b72461 to your computer and use it in GitHub Desktop.
Table Heirarchy
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment