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