Scripting a Query to Join All Tables in the database based on relationships

Question

I pulled some SQL from various stack overflow questions, to help me pull my relationships in my database. This was very helpful. (I dropped all this into a #temptable).

SELECT  fk.name 'FK Name',
        tp.name 'Parent table',
        cp.name, cp.column_id,
        tr.name 'Refrenced table',
        cr.name, cr.column_id
FROM    sys.foreign_keys fk
INNER JOIN  sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN  sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN  sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN  sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN  sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY    tp.name, cp.column_id

This was done to create scripts that have Joins/etc on all my tables for all my relationships. Now I have a little bit of a dilemma I'm not entirely sure how to do this.

For example the output of the above shows the following:

It shows a parent table called Business with a series of fields called ID_BsinessCategory, ID_BusinessGroup, etc. They are linked to Reference Tables called BusinessCategory and BusinessGroup.

What I need to do is write a script that will create joins for me

select * 
from Business a
join BusinessCategory b on a.ID_BusinessCategory = b.ID_BusinessCategory 

The issue is not that I can't write the code, the issue is I don't know how to script the database to write it for me. There are almost 150 tables with comparable amounts of relationships.

Any advice would be appreciated.

Answer 1

This might be a good starting point

DECLARE @join_query NVARCHAR(MAX)

DROP TABLE #temptable

SELECT  fk.name  AS 'FK Name',
        tp.name AS 'Parent table',
        cp.name AS parent_col, 
        cp.column_id AS parent_col_id,
        tr.name AS 'Refrenced table',
        cr.name AS child_col_id, 
        cr.column_id
INTO #temptable
FROM    sys.foreign_keys fk
INNER JOIN  sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN  sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN  sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN  sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id

INNER JOIN  sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY    tp.name, cp.column_id

SELECT @join_query =  CONCAT(
    'SELECT * FROM ', [Parent table],
    STRING_AGG(
        ' JOIN ' + 
        [Refrenced table] + 
        ' ON [' + [Parent table] + '].' + parent_col + ' = [' +
        [Refrenced table] + '].' + child_col_id + ' '
    , '')
)   
FROM #temptable
GROUP BY [Parent table]


EXEC sp_executesql @join_query

What the above statement does after recreating your temporary table is to build up a statement that concatinates these relationships into JOINs, grouping by each parent table. After this it executes the statement stored in @join_query.

From here you'll probably want to add to that statement something that stores the result such as insert into another temp table or you could even have it create a view.