Is it possible to list all foreign keys in a database?

Question

How can I list all FKs in sqlserver database?

Answer 1

I use this statement, it works very well.

SELECT RC.CONSTRAINT_NAME FK_Name , KF.TABLE_SCHEMA FK_Schema , KF.TABLE_NAME FK_Table , KF.COLUMN_NAME FK_Column , RC.UNIQUE_CONSTRAINT_NAME PK_Name , KP.TABLE_SCHEMA PK_Schema , KP.TABLE_NAME PK_Table , KP.COLUMN_NAME PK_Column , RC.MATCH_OPTION MatchOption , RC.UPDATE_RULE UpdateRule , RC.DELETE_RULE DeleteRule FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME 

Answer 2

It is theoretically difficult. The relational model allows any field to relate to any other field. Which ones are actually used are determined by all possible SELECT statements that can be used.

In practice, this depends on how many tables contain FK definitions. If someone has bothered to carefully identify all of the FK links - and SELECT statements adhere to these rules - you can request them.

However, since a SELECT statement can join anything, there is no guarantee that you have all FK unless you also have all SELECT statements.

---

Edit

See http://www.lostechies.com/blogs/jimmy_bogard/archive/2008/11/26/viewing-all-foreign-key-constraints-in-sql-server.aspx

 SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id 

You can work for you.

Answer 3

I am using http://technet.microsoft.com/en-us/library/ms189807.aspx

 SELECT * FROM sys.foreign_keys 

That is, if you are at least on SQL Server 2005+ and want to see a list of FK names.

But you probably want to know more about table related ones, right, that's where the answer comes in handy.

Answer 4

You can also use the canonical INFORMATION_SCHEMA schema in SQL Server 2005 onwards:

How to find foreign key dependencies in SQL Server?

This will work in other databases as well.

Answer 5

Here is a more informative way of presenting it.

 SELECT DISTINCT PARENT_TABLE = RIGHT(Replace(TC.constraint_name, 'FK_', ''), Len(Replace(TC.constraint_name, 'FK_', '')) - Charindex('_', Replace(TC.constraint_name, 'FK_', ''))), CHILD_TABLE = TC.table_name, CU.column_name, TC.constraint_name, TC.constraint_type FROM information_schema.table_constraints TC INNER JOIN information_schema.constraint_column_usage CU ON TC.constraint_name = CU.constraint_name WHERE TC.constraint_type LIKE '%foreign' OR TC.constraint_type LIKE '%foreign%' OR TC.constraint_type LIKE 'foreign%'