User Location
List tables linked to a parent table
Posted by codingsense on December 23, 2008
Many times we require to know which child tables are related to the parent table. The following query gives us the tables are related to the parent table with foreign keys.
select TABLE_NAME from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE CONSTRAINT_NAME IN( SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE UNIQUE_CONSTRAINT_NAME IN ( select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE where table_name ='ParentTableName'))
Just replace the string ParentTableName with the table name for which you want to search the related child tables.
Happy Learning 
