SQL SERVER - 2005 - Find Tables With Foreign Key Constraint in Database - Part 2

Feed on: Posts; Comments

SQL SERVER - 2005 - Find Tables With Foreign Key Constraint in Database - Part 2

May 8, 2008 by pinaldave

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:

Hi Pinal

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.

USE AdventureWorks
GO
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,
            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://www.SQLAuthority.com), Joffery

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 3 Comments

3 Responses to “SQL SERVER - 2005 - Find Tables With Foreign Key Constraint in Database - Part 2”

on July 1, 2008 at 1:50 am Ivan

thanks a lot, you saved me so much time!
on August 13, 2008 at 10:14 pm Bob Schultz

Pinal,

This article was a great help to me.

I was wondering if you could add schema columns to the result set?

Thanks again..

Bob
on December 18, 2008 at 1:42 am Todd Owens

Bob Shultz:

Schema Names added below

USE AdventureWorks
GO
SELECT
f.name AS ForeignKey,
SCHEMA_NAME(o.schema_id) AS SchemaName,
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,
f.update_referential_action_desc AS UpdateAction,
f.delete_referential_action_desc AS DeleteAction
FROM sys.foreign_keys AS f
JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
JOIN sys.objects o
ON f.parent_object_id = o.object_id
ORDER BY
SchemaName ASC,
TableName ASC,
ColumnName ASC