3

Imagine you inherit a database which has a bunch of tables, no documentation and zero referential integrity. Now you're trying to find out what are the various relationships between different tables.

What process would you go through to do this e.g. finding common data patterns, matching these across tables?

  • 1
    Is the app in use? If so you could run extended events or a trace to see what queries are running; this will give you a good solid understanding of the most used tables. Are tables and columns named using something approaching sanity, or are they named like C00001 ? – Max Vernon Feb 1 '16 at 15:18
  • If there are stored procedures (or views! or triggers!) you could manually inspect the code to see what joins are made and what else might happen to data after it is inserted/updated. If there are no stored procedures and the app isn't actively being used (or not enough to garner info from trace/extended events), but has been used sine the last restart, you could gather info from dm_exec_query_stats and the procedure cache. – Aaron Bertrand Feb 1 '16 at 15:20
  • How many tables is a bunch of tables? If it's a fairly small number and the table and column names are meaningful or at least consistent (e.g. customer_id is the same in all tables), might be worthwhile to script out the tables and write a recursive script to find column names that appear in multiple tables. If it's really few tables (a dozen or two), just scripting out the tables & columns (ordered alphabetically) and eyeballing them on a spreadsheet might be fastest. – SQLmojoe Feb 1 '16 at 16:25
  • I mean it's a mess, at some point someone who "designed" this thing decided to change mytable to my_table (for multiple tables) and by change I mean keep both versions. The database itself is not large < 1gb so examining every single value on the database would not be a lengthy exercise timewise – adolf garlic Feb 1 '16 at 19:36
0

You could programmatically build queries to test which combinations of columns look like they might be keys. In order to test a given relationship you'd run a query of the form:

SELECT 1 AS [IsPossibleKey: ChildTable.KeyColumn => ParentTable.PrimaryKeyColumn]
WHERE NOT EXISTS (
 SELECT *
 FROM ChildTable c
 WHERE NOT EXISTS (
  SELECT *
  FROM ParentTable p
  WHERE c.KeyColumn = p.PrimaryKeyColumn
 )
)

This can give you a fairly raw list of key relationship candidates. I would write a SQL query to programmatically generate all possible queries of the above form, paste all of them into a new SSMS window and run time.

You probably should exclude candidates with zero non-NULL values in the child table column.

Another problem is that bad data might have slipped in. Heck, since there are no FKs it almost certainly has slipped in somewhere.

Also, this only finds single-column combinations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.