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

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.  
Tagged:

Answers

  • ThomasBEThomasBE Posts: 6 Bronze 1
    edited November 9, 2022 11:59AM
    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_KirkwoodJon_Kirkwood Brisbane, Australia Posts: 132 Silver 1

    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.

     

     

    Jon Kirkwood | Technical Support Engineer | Redgate Software
Sign In or Register to comment.