CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1076439
user1076439

Reputation: 111

Find foreign key relationships with a SQL query

Is it possible to find foreign key relationships with a SQL query?

The database is external to me and I am unable to access it directly to see the links through Server Management Studio.

Upvotes: 3

Views: 7474

Answers (3)

Adam Wenger
Adam Wenger

Reputation: 17540

To retrieve a list of Foreign keys, you can run this query:

SELECT t.name AS FKTableName
   , fk.name AS NameOfForeignKey
   , pc.name AS FKColumn
   , rt.name AS ReferencedTable
   , c.name AS ReferencedColumn
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.tables AS t ON fkc.parent_object_id = t.object_id
INNER JOIN sys.tables AS rt ON fkc.referenced_object_id = rt.object_id
INNER JOIN sys.columns AS pc ON fkc.parent_object_id = pc.object_id
   AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS c ON fkc.referenced_object_id = c.object_id
   AND fkc.referenced_column_id = c.column_id

This query can also be made more complex if you require additional information for your setup.

Upvotes: 11

Valentin V
Valentin V

Reputation: 25729

To find about foreign keys, consider using these system views:

sys.sysconstraints
sys.columns
sys.tables

You can join these three views on colid, column_id, object_id to get full information about the foreign key constraints.

These views should be in SQL Server 2005+

Upvotes: 3

BizApps
BizApps

Reputation: 6130

No you cant,

But you can check it manually,it is cumbersome where you need to query each table and to check what are the columns[id's] exist in each different tables. To see it's relationships.

Regards

Upvotes: -2

Related Questions