Recursive Loops for Dependent objects

We have a database and non of the tables are linked(PK_FK relation). Some of the tables are loaded by SSIS and some by procedures,functions and views. I was trying to make an excel that can be maintained to have a track of the flow of data.

Is there any way that we can get the flow using t-sql queries? what about a recursive loop which find the dependent items and loops through them. problem I am facing is what about a child has 2 child and both have another sub child.

It is much easy to work on this scenario when we have primary-foreign key relationship but unfortunately we don't have that.

Please provide:

  • table definitions in the form of create statements
  • sample data in the form of insert statements
  • expected output from the sample data you provide
  • the query you are stuck at
  • errors you might get from your query