Pfthb

I need to get sample data from all the database tables, there are hundreds of tables and not all of them may be relevant.

Is there a way I can provide an id from the top level table, to select a row, and also select the corresponding row of data in the subtables (if data exists).

i.e. Given an id, select all the data for this id in all the tables, only return data if it exists.

e.g. Instead of doing:

Select * from main_table where id = 1
Select * from next_table where master_id = 1
Select * from another_table where master_id = 1
Select * from sub_table where next_table.id = 5
Select * from this_table where sub_table.id = 9

etc

There are 2 options, I can see.

Option #1: multiple joins
You will have to join the main_table with other required tables to select the top 1 from each table based on the reference keys.

declare @main_table_id int
set @main_table_id = 1

Select top 1 * from main_table where id = @main_table_id

Select top 1 nt.* 
from main_table mt
inner join next_table nt on mt.id = nt.master_id 
where mt.id = @main_table_id

Select top 1 at.* 
from main_table mt
inner join another_table at on mt.id = at.master_id 
where mt.id = @main_table_id

Select top 1 st.* 
from main_table mt
inner join next_table nt on mt.id = nt.master_id 
inner join sub_table st on st.next_table_id = nt.id
where mt.id = @main_table_id

Select top 1 st.* 
from main_table mt
inner join next_table nt on mt.id = nt.master_id 
inner join sub_table st on st.next_table_id = nt.id
inner join this_table tt on tt.sub_table_id = st.id
where mt.id = @main_table_id

Option #2: Stored procedure style
you will be selecting the keys and storing in a variable. This could end up in having a lot of SQL variable.

declare @main_table_id int
set @main_table_id = 1

Select top 1 * from main_table where id = @main_table_id

declare @next_table_id int
select top 1 @next_table_id = id from next_table where master_id = @main_table_id
Select top 1 * from next_table where id = @next_table_id

Select top 1 * from another_table where master_id = @main_table_id

declare @sub_table_id int
select top 1 @sub_table_id = id from sub_table where next_table_id = @next_table_id
Select top 1 * from sub_table where id = @sub_table_id 

Select top 1 * from this_table where sub_table_id = @sub_table_id