What I love most about this blog is active readers participation. If readers are becoming contributor is the true success for any blog or online community. Recently many readers have contributed their suggestions and script to this blog.
Joffery has provided nice script which is modification to previous article of SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database.
Following note is from Joffery:
Very interesting article and of great help.
I made a little addition to your code. As I wanted also to know what the FKs are doing in the Table (referential integrity on update and on delete) I added two columns to your superb query. And a little bit of ordering :)
Maybe it helps other readers.
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
fc.referenced_column_id) AS ReferenceColumnName,
f.update_referential_action_desc AS UpdateAction,
f.delete_referential_action_desc AS DeleteAction
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
ORDER BY TableName ASC, ColumnName ASC
Reference : Pinal Dave (http://blog.SQLAuthority.com), Joffery