Discover intended Foreign Keys from JOINS in scripts
- by Jason
I'm inheriting a database that has 400 tables and only 150 foreign key constraints registered. Knowing what I do about the application and looking at the table columns, it's easy to say that there ought to be a lot more.
I'm afraid that the current application software will break if I started adding the missing FKs because the developers have probably come to rely on this "freedom", but step one in fixing the problem is to come up with the list of missing FKs so we can evaluate them as a team.
To make matters worse, the referencing columns don't share a naming convention.
The relationships ARE coded informally into the hundreds of ad-hoc queries and stored procedures, so my hope is to parse these files programmatically looking for JOINS between actual tables (but not table variables, etc).
Challenges I foresee in this approach are: newlines, optional aliases and table hints, alias resolution.
Any better ideas? (Besides quitting)
Are there any pre-built tools that can solve this?
I don't think regex can handle this. Do you disagree?
SQL Parsers? I tried using Microsoft.SqlServer.Management.SqlParser.Parser but all that is exposed is the lexer - can't get an AST out of it - all that stuff is internal.
