CODEWITHSUNDEEP
sql-server
Byron Whitlock
Byron Whitlock

Reputation: 53850

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

How do I list all FK's in a sqlserver database?

Upvotes: 63

Views: 58551

Answers (5)

Prodip Sarker
Prodip Sarker

Reputation: 1

select * from sys.objects where type = 'F'

Upvotes: 0

ArsedianIvan
ArsedianIvan

Reputation: 389

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%'

Upvotes: 1

Pricey
Pricey

Reputation: 5929

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

SELECT * FROM sys.foreign_keys

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

But you probably want to know more about the tables that are related too, don't you, that is where the answer comes in handy.

Upvotes: 10

jsr
jsr

Reputation: 1329

I use this statement, it seems to work pretty 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

Upvotes: 118

S.Lott
S.Lott

Reputation: 391818

Theoretically, this is hard. The relational model allows any field to relate to any other field. Which ones are actually used is defined by all possible SELECT statements that could be used.

Practically, it depends on how many tables have the FK definitions included. If someone bothered to carefully define all FK references -- and the SELECT statements stick to these rules -- you can query them.

However, since a SELECT statement can join on anything, there's no guarantee that you have all FK's 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

Might work for you.

Upvotes: 11

Related Questions