SQL Server: find relations between tables without Foreign Key constraints

2
votes

I have a SQL Server database with lot of tables (several hundrends), somehow related with each other. All of them have Primary Keys (GUID), but only few of them have actually defined Foreign Key constraints.

I need to find all tables related with certain table (let's call it TargetTable) both related directly and inderectly (through 1, 2 or more intermediate tables) on any column.

My finish goal to get SQL queries (one per each related table) which JOIN all tables between TargetTable and that related table.

For example: it's found 5 related to TargetTable tables:

  • TargetTable - Table1
  • TargetTable - Table1 - Table2
  • TargetTable - Table3
  • TargetTable - Table3 - Table4
  • TargetTable - Table3 - Table4 - Table5

I need to get 5 separate JOINs.

It there any SQL query or software or utility or any way to get desired SQL codes? Or even enough to get relations in some convinient graph so i could parse them with my favourite script language and generate SQL codes.

3
sqlsql-serverdatabaseforeign-keysrelationship

3 Answers

4
votes

You can certainly generate code by looping through information_schema.columns or sys.columns but I doubt this is going to work as well as you would like.

If they didn't bother to put in FKs then they probably have done some other awful things.. like no standard naming conventions or generic tables.

You are probably better off looking through the SQL queries/procedures in the database to see where most of the relationships are... then you will have to decide for yourself if tables are related or not.

0
votes

You can use SQL Server Management Studio to have both, graph with database diagrams (Not ideal but usefull) https://www.mssqltips.com/sqlservertip/1816/getting-started-with-sql-server-database-diagrams/ and you can get SQL and joins with Query Designer (Still in SSMS) https://www.mssqltips.com/sqlservertip/1086/sql-server-management-studio-query-designer/

Hope this help,

0
votes

You cannot infer relations given the tables only. To do that you need to have knowledge of the domain. For example, suppose you have two tables, T1 which contains and int field X and, T2 which has an int field Y. Then there is a relationship R, between the rows of T1 and T2, where (r1,r2) is in R if and only if r1.x = r2.y.

So I would suggest that you construct a model (ER-model for example) using your knowledge of the domain. Then add the foreign key constraints manually.