Finding all related tables to a given table

Question

I'm working on a development with over 700 tables. I need to see a listing of all related tables to a given table. Can you recommend an app that is able to provide such a thing.

Answer 1

Depending on the database product, you should be able to query the INFORMATION_SCHEMA views like so:

Select FK.TABLE_SCHEMA, FK.TABLE_NAME From INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS As RC     Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As PK         On PK.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME     Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As FK         On FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME Where PK.TABLE_SCHEMA = 'dbo'     And PK.TABLE_NAME = '<target table name>'    

Answer 2

If your database supports the information schema views (most do), then you can run this query:

SELECT     c.CONSTRAINT_NAME,     cu.TABLE_NAME AS ReferencingTable, cu.COLUMN_NAME AS ReferencingColumn,     ku.TABLE_NAME AS ReferencedTable, ku.COLUMN_NAME AS ReferencedColumn FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu ON cu.CONSTRAINT_NAME = c.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON ku.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME 

This will output a list of all referential constraints (foreign keys), the source (referencing) table/column, and primary key (referenced) table/column.

If you want to see references to a specific table, just add:

WHERE ku.TABLE_NAME = 'SomeTable'