2

I have a list of database table.They have interlinked(pk-fk) relationship are maintained by the logic.In the database level no such linkage is there.Is there any tool or any process to identify the linkage.which will reduce lot of manual task.

Actually i want what are the primary key(pk) and foreign key(fk) and what are the tables that are linked.

Ankur Alankar Biswal
  • 1,184
  • 1
  • 12
  • 23
  • 2
    In the systems that I'm involved with, foreign key relationships have a very clear naming scheme that make the relationship obvious. – Gordon Linoff Mar 13 '15 at 11:58

1 Answers1

1

You can check Getting started with SQL Server database diagrams. Also there is a tool SchemaCrawler which you can use to find the relationship between the tables

Also you can use this query which will tell the reference to your tables:

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    cp.name, cp.column_id,
    tr.name 'Refrenced table',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
    tp.name, cp.column_id

Source

Community
  • 1
  • 1
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • It will provide the metadata about the database.But problem is that is database i don't have fk(in programming logic i have relationship).I have only pk in database.So how this will help me.Suggest me if i am not correct.. – Ankur Alankar Biswal Mar 13 '15 at 13:09
  • SchemaCrawler is able to infer foreign key relationships, even if they are not expressed foreign key relationships in the database. – Sualeh Fatehi Mar 14 '15 at 04:51