Menu
How do I list all FK's in a sqlserver database?
770
To see foreign key relationships of a table: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA. KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db_name' AND REFERENCED_TABLE_NAME = 'table_name';
The most Simplest one is by using sys. foreign_keys_columns in SQL. Here the table contains the Object ids of all the foreign keys wrt their Referenced column ID Referenced Table ID as well as the Referencing Columns and Tables.
If we want to know the table's primary keys and foreign keys. We can simply use an “information_schema. key_column_usage” view, this view will return all of the table's foreign keys and primary keys.
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 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.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
