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.
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.name as column_name,
t.name as data_type,
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,
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.
Tips and how-to guides for Redgate products
Ask, discuss, and solve questions about Redgate's tools
Meet us at an event, get sponsored, and join our Friends of Redgate
In-depth articles and opinion from Redgate's technical journal