CODEWITHSUNDEEP
sql
gillian
gillian

Reputation: 421

Finding all related tables to a given table

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.

Upvotes: 36

Views: 45163

Answers (5)

VladimirK
VladimirK

Reputation: 37

In MS SQL Server you can use system stored procedure:

exec  sp_help 'table_name'

It show a lot of information about table and included list of the tables is referenced by foreign key.

Upvotes: 1

Eduard Streltsov
Eduard Streltsov

Reputation: 1956

I use DataGrip diagrams with "Key columns" enabled and just "Columns" disabled. It's not a list of course, and I can't imagine how it looks like on a DB with 700 tables, but it's the most convenient way for me to see all relations between tables in my project.

Upvotes: 0

Paul Sasik
Paul Sasik

Reputation: 81557

MS Visio Pro can do a good job of reverse engineering a db schema for a variety of databases (via ODBC) and provides a diagram that is very flexible.

Word of caution: Regardless of what system you use you do not want to pull in all 700 tables all at once. Try to create several diagram grouped and separated logically. Hopefully there will be many such groupings in a database that large.

Upvotes: 1

Thomas
Thomas

Reputation: 64674

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>'

Upvotes: 10

Aaronaught
Aaronaught

Reputation: 122684

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'

Upvotes: 56

Related Questions