SQL tool to list the related tables to the parent table

Question

Dears,

Could you please help me to find a SQL tool that will show me the list of the tables that have relation with the parent table. Example:

Select * from table_1

when I write join there will be listed tables that are related to the table_1 and after choosing one of the tables, it will also show the join based on the column PK and FK.

I have tried SQL promt but it is listing all the objects, not only the ones that I could join with table_1.

I am working on a DB with more than 300 tables and it is hard to find the relations between tables.

Thanks!

What exactly is your question here? To find relationships between tables look at sys.sysforeignkeys. https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysforeignkeys-transact-sql — Sean Lange, Feb 16 '18 at 17:13
I havent needed this myself yet but I did look into it a few weeks ago out of curiosity. Looks like they added this functionality to sqlprompt - https://forum.red-gate.com/discussion/5333/special-icon-for-foreign-key-relationship-join-candidates — Joe C, Feb 16 '18 at 18:59
I just did a quick check. The join suggestion had the foreign key relationship a the top of the list with an icon of a key next to a paperclip. — Joe C, Feb 16 '18 at 19:01

score 0 · Answer 1 · answered Feb 16 '18 at 18:54

This query would help you find tables that reference a given table. You could also rework the query to give you the inverse if needed.

SELECT  
     KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
    ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
    ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 
where KCU2.TABLE_NAME  = 'ReferencedTableName'

SQL tool to list the related tables to the parent table

1 Answers1