s there a way to use Redgate to identify possible table relationships.

Drw001

I inherited a legacy database with several table with primary keys but no foreign key relationships. Is there a way to use Redgate to identify possible table relationships.  

ThomasBE

Using SQL Monitor to capture queries and plans to search for joins could be a start ? 

You could focus on most resource consuming queries first allowing you to quickly solve the most pressing issues?

Jon_Kirkwood

Hi @Drw001

 Thank you for reaching out on the Redgate forums regarding this relationship query.

It may be a hard one to define a specific method to find the possible relationships. 

• Dependency Tracker is the primary solution but does rely on the relationships existing to function. 

• SQL Monitor would be a possible method as @ThomasBE describes, it wouldn't explicitly find results but would give you a better reporting tool on what is being called to help prioritise relationships on often used objects.

•  SQL Search may be a viable option to search for JOIN or similar keywords on your databases to look for objects joining tables as part of their function.

 

 With your legacy database, is there any specific logic that would be used to determine if a relationship should exist. Matching object name/data type for instance?

It may be possible to script it to output all the table columns to work through and look for matches. 

SELECT schema_name(tab.schema_id) as schema_name,
    tab.name as table_name,
    col.column_id,
    col.name as column_name,
    t.name as data_type,   
    col.max_length,
    col.precision
FROM sys.tables as tab
    INNER JOIN sys.columns as col
        on tab.object_id = col.object_id
    LEFT JOIN sys.types as t
    on col.user_type_id = t.user_type_id
ORDER BY column_name,
    table_name,
    column_id;

 

I tested this by running the script, output the result to Excel and highlighting duplicate column_names. Gave me a starting point to look for possible relationships.