CODEWITHSUNDEEP
sqlsql-server
Chakravarthi Tata
Chakravarthi Tata

Reputation: 45

How can we identify the relationship between two SQL Server tables , either one to one or some other relationship.....?

How can we identify the relationship between two SQL Server tables, either one to one or some other relationship.....?

Upvotes: 3

Views: 120

Answers (3)

Torsten
Torsten

Reputation: 11

You can use Microsoft system views for that purpose:

SELECT  
    obj.name AS fk
    ,sch.name AS [schema_name]
    ,tabParent.name AS [table]
    ,colParent.name AS [column]
    ,tabRef.name AS [referenced_table]
    ,colRef.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id
JOIN sys.tables tabParent ON tabParent.object_id = fkc.parent_object_id
JOIN sys.schemas sch ON tabParent.schema_id = sch.schema_id
JOIN sys.columns colParent ON colParent.column_id = parent_column_id AND colParent.object_id = tabParent.object_id
JOIN sys.tables tabRef ON tabRef.object_id = fkc.referenced_object_id
JOIN sys.columns colRef ON colRef.column_id = referenced_column_id AND colRef.object_id = tabRef.object_id
JOIN sys.schemas schRef ON tabRef.schema_id = schRef.schema_id
WHERE schRef.name = N'dbo'
AND tabRef.name = N'Projects'

It is possible to filter this query by referenced tables or columns, or just to look for everything that references a specific column.

Upvotes: 1

jengacode
jengacode

Reputation: 128

In SQL Server, create a new view in your database, add the two tables you want to see the relationship with.

enter image description here

Upvotes: 1

Viplock
Viplock

Reputation: 3319

If you wanna check what all tables are connected to the table, Just right click on table and go to View dependencies

View dependencies of a table and find out. And you can check all constrains by checking create query for that table.

check all table label constraints in its query

i think it will help you out . thanks

Upvotes: 1

Related Questions