1

There is a very old product, that has more than 1500 tables and there is no foreign key relationship defined between tables.

Is there any way so we can find out the relationship of tables?

Thanks!

Raj
  • 313
  • 2
  • 9

2 Answers2

1

Without any FK's, it's impossible to determine, exactly, what, if any, relationships tables have. You might be able to determine this in an application that calls the DB. For example, if an application uses Entity Framework or Linq to SQL and uses the DB, the relationships could be defined in the model, even thought they aren't defined in the DB. Even without this, it might be possible to determine relatioships by examining how data is rerieved and classes created from this data.

Randy Minder
  • 47,200
  • 49
  • 204
  • 358
0

If there's no relationships between tables then there's no relationships between tables. Anything that could be done would be somewhat guesswork and the results will be mediocre at best, and that would all depend on how the original developers named the tables and columns.

Something that could potentially get you started would be the following query:

select distinct t1.name
from sys.columns s1
inner join sys.tables t1 on t1.object_id = s1.object_id
inner join sys.columns s2 on s1.name = s2.name
 and s1.object_id <> s2.object_id
inner join sys.tables t2 on t2.object_id = s2.object_id

this will get you a list of tables that have a column with an exact match with another table. Note there will be a lot of noise returned from this query (most likely) but it might be a help to get you started.

Kritner
  • 13,557
  • 10
  • 46
  • 72
  • Thanks Raj, yeah I know it wasn't you that downvotes you don't have the rep quite yet ;). Was curious about the vote because as far as I know, there really is no real "good" way to do this. Randy's answer and mine would both be "potential" methods of arriving at a solution, but it would be a very labor intensive process and prone to errors. – Kritner Nov 10 '14 at 13:49
  • Can you please tell me is there any tool that will pick the Primary Column name and column data type and will look for all the other available table's same data type column and will compare the data of columns. – Raj Nov 10 '14 at 13:50
  • if data is matching then possibly there will relation ship in those both tables. – Raj Nov 10 '14 at 13:52
  • @RajKumar you can take a look at: http://stackoverflow.com/questions/3930338/sql-server-get-table-primary-key-using-sql-query to find table primary keys with SQL, not sure how you would plug that info into your view of the data however. Might be enough to get you started. – Kritner Nov 10 '14 at 14:09