How do I list all FK's in a sqlserver database?
No! Bad! See http://meta.stackoverflow.com/questions/8231/are-answers-that-just-contain-links-elsewhere-really-good-answers
jason
2009-08-04 21:17:46
+1
A:
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.
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
2009-08-04 21:15:49
All defined foreign keys are defined. How would I query the defined foreign keys?
Byron Whitlock
2009-08-04 21:18:39
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
2009-08-04 21:29:15
@Byron Whitlock: "All" rarely means all, it usually means "All but for a few exceptions".
S.Lott
2009-08-04 21:31:52
@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
2009-08-04 21:33:02
I wonder where in the original question it is mentioned that the only interesting FKs are the actually used ones...
Vinko Vrsalovic
2009-08-04 21:36:38
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
2009-08-04 21:37:57
@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
2009-08-04 21:48:42
@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
2009-08-04 22:12:20
A:
You can also use the canonical INFORMATION_SCHEMA schema in SQL Server 2005 onwards:
http://stackoverflow.com/questions/925738/how-to-find-foreign-key-dependencies-in-sql-server
This will work in another databases as well.
Vinko Vrsalovic
2009-08-04 21:43:26
+1
A:
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
2009-08-04 21:57:14