63

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

Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168

5 Answers5

117

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
jsr
  • 1,319
  • 1
  • 10
  • 5
  • 4
    Beware! `INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS` omits some foreign keys that `sys.foreign_keys` returns (in my case, a foreign key with `Enforce For Replication` set to `No`. I ended up using this answer instead: http://stackoverflow.com/a/10697248/14731 – Gili Aug 20 '15 at 21:35
  • 1
    You'll need to add this to handle compound keys: `AND KF.ORDINAL_POSITION = KP.ORDINAL_POSITION` – Tom Warfield Apr 16 '20 at 17:04
12

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.

S.Lott
  • 384,516
  • 81
  • 508
  • 779
  • All defined foreign keys are defined. How would I query the defined foreign keys? – Byron Whitlock Aug 04 '09 at 21:18
  • 1
    This isn't in any way an answer to the question. A Foreign Key is a real thing, an object in the database (or at least a property of an object). The question doesn't ask to list every possible column that you can join against, that wouldn't make any sense – Adam Batkin Aug 04 '09 at 21:29
  • @Byron Whitlock: "All" rarely means all, it usually means "All but for a few exceptions". – S.Lott Aug 04 '09 at 21:31
  • 1
    @Adam Batkin: The Relational model permits every possible column to be a foreign key. That's the definition. There's no restriction imposed anywhere. FK's defined in SQL server are an optimization for some queries. Until you have ALL the SELECTS, you don't really knwo. – S.Lott Aug 04 '09 at 21:33
  • I wonder where in the original question it is mentioned that the only interesting FKs are the actually used ones... – Vinko Vrsalovic Aug 04 '09 at 21:36
  • Aditionally, he's asking concretely about the defined FKs, not the potential FKs implicitly defined by SELECTs. I think you are reading too much into the question. – Vinko Vrsalovic Aug 04 '09 at 21:37
  • 1
    @S.Lott: I disagree. It is clear from the question, and all common usage that I have ever seen (including Wikipedia, which for computing definitions is usually pretty accurate) that the question is definitely about the database object (that exists in ALL ACID RDBMSs) "FOREIGN KEY". – Adam Batkin Aug 04 '09 at 21:48
  • @Adam Batkin: I agree the question is about "defined" foreign keys>. The question is a bad question because defined FK's are not not **ALL** foreign keys. I think it helps to note when the question -- as asked -- is incomplete or inaccurate. – S.Lott Aug 04 '09 at 22:12
  • A relational model FK is a set of columns whose subrow values elsewhere as a CK. FKs are not needed to join & joinability says nothing about FKs. But that is irrelevant because the question is asking about SQL & evidently means the declared conceptual SQL things characterized by a name & 2 tables with column lists where referencing subrow values must appear as UNIQUE referenced subrow values. – philipxy Jul 26 '19 at 03:13
9

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.

Pricey
  • 5,799
  • 12
  • 60
  • 84
1

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%'
dYale
  • 1,541
  • 1
  • 16
  • 19
ArsedianIvan
  • 369
  • 2
  • 6
  • 20
0
select * from sys.objects where type = 'F'
Martin Brisiak
  • 3,872
  • 12
  • 37
  • 51
Prodip Sarker
  • 1
  • 1