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
The SQL Dude! 
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
Sudarshan Narasimhan said
Thanks John. Good catch about constraint_object_id . I’ve maded the changes, cheers!
Desalegn said
Great Script !!
Brad Wallace said
Very useful, thanks!
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
Mario said
It doesn’t display correct schema name, rather alwayse ‘dbo’ ?? is it a bug?
Ban Ăn Chơi said
Thanks, nice tool
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).
Sudarshan Narasimhan said
Thanks for pointing that out. Script updated with the fix.