The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

TSQL script to find Foreign Key Dependencies on entire database

Posted by Sudarshan Narasimhan on February 14, 2012



 

Often in the life of a DBA/Developer when we have to test something on a database, we are not always aware of the schema/design since it was created by someone else or you inherited the project. Lets says you want to test a particular SP execution time, but the data sample is too big for you to wait for the restore, so you want to insert some sample data. More often that not, the constraints defined do not let you to modify/add/remove data from the base tables. Well, you can’t pull up the entire database diagram and start studying the design.

It is not practical to do this on the entire database with Management Studio even though it offers a nice View dependencies option, but this is on a table by table basis. You would need to use T-SQL for this.

 

Here is a script which you can run on the entire database to understand which tables are dependent on your Table X,Y,Z and what are the columns being referenced (foreign keys) and what is the action on update/delete. You can uncomment the filter in the WHERE clause if you want to specify only some table names.

Script Modified: April 18, 2012

 

SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(fk.referenced_object_id), clm1.name) as 'S.No',
       fk.referenced_object_id as ReferencedObjID,
       constraint_column_id as ColumnID,
       OBJECT_NAME(fk.referenced_object_id) as [ReferencedTable(Parent)],
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.referenced_object_id,N'SchemaId') AS tinyint)) as [ParentSchema],
       clm2.name as ReferencedColumnName,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       fk.parent_object_id as ReferencingObjID,
       OBJECT_NAME(fk.parent_object_id) as [ReferencingTable (Foreign)],
       clm1.name as ForeignKeyColumn,
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.parent_object_id,N'SchemaId') AS tinyint)) as [ForeignSchema],
       [Action on Update] = CONVERT(varchar,CASE OBJECTPROPERTY(constraint_object_id,'CnstIsUpdateCascade')  
                                        WHEN 1 THEN 'CASCADE'
                                        ELSE 'NO_ACTION'
                                      END), 
       [Action on Delete] = CONVERT(varchar,CASE OBJECTPROPERTY(constraint_object_id,'CnstIsDeleteCascade')  
                                        WHEN 1 THEN 'CASCADE'
                                        ELSE 'NO_ACTION'
                                      END)
FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1 
         ON fk.parent_column_id = clm1.column_id 
            AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2
         ON fk.referenced_column_id = clm2.column_id 
            AND fk.referenced_object_id= clm2.object_id
 --WHERE OBJECT_NAME(fk.referenced_object_id) = 'TBL_TEST'  --- table name which is being referenced by other tables via Foreign Keys
 ORDER BY OBJECT_NAME(fk.referenced_object_id)

 

Here is the how the output looks like on the AdventureWorks database

image

Advertisements

9 Responses to “TSQL script to find Foreign Key Dependencies on entire database”

  1. John said

    Great script! only a small bug found:

    constraint_object_id should be used in OBJECTPROPERTY(fk.referenced_object_id,’CnstIsUpdateCascade’) and in OBJECTPROPERTY(fk.referenced_object_id,’CnstIsDeleteCascade’)

    instead of fk.referenced_object_id.

    Otherwise all the entries are null… and as per MSDN’s note (in http://msdn.microsoft.com/en-us/library/ms176105(v=sql.90).aspx): “Unless noted otherwise, NULL is returned when property is not a valid property name, id is not a valid object ID, id is an unsupported object type for the specified property, or the caller does not have permission to view the object’s metadata. ”

    Thanks for the great script

  2. Desalegn said

    Great Script !!

  3. Very useful, thanks!

  4. Tony W said

    Only one small issue, when you have more than 9 schemas (or have created/removed more than 9…) schemas, as you convert the objectpropertyex to bit, it will always find .dbo (schema 1) providing you have between 10 and 19 schemas.
    If you alter the line to read “SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.parent_object_id,N’SchemaId’) AS INT)) as [ForeignSchema]”, casting it as an INT, the correct schema is displayed

  5. Mario said

    It doesn’t display correct schema name, rather alwayse ‘dbo’ ?? is it a bug?

  6. Thanks, nice tool

  7. binh cao said

    Script has issue with returning only dbo schema because the cast for line that has SCHEMA_NAME is bit. it needs to be changed from bit to tinyint like so

    SCHEMA_NAME (CAST……) as bit —-> SCHEMA_NAME (CAST……) as tinyint.

    the schema_id is a number 1,2,3,4,5, etc. not just 0 or 1 (dbo).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: