CODEWITHSUNDEEP
sql-server-2012
Swiftly
Swiftly

Reputation: 57

Find all the related tables

I have a database with around 1000 tables and I need to find all tables that relate to a specific one, let's say the customer table. How do I proceed?

Upvotes: 3

Views: 10420

Answers (1)

Joe Alfano
Joe Alfano

Reputation: 10299

When you ask to find all tables that relate to a specific one, I assume you are asking for all tables that have a foreign key reference to your "customer" table. This is closely related to a previous Stack Overflow question. The following query that uses the system catalog views should do the trick:

select t.name as TableWithForeignKey, fk.constraint_column_id as FK_columns , c.name as ForeignKeyColumn 
    from sys.foreign_key_columns as fk
        inner join sys.tables as t on fk.parent_object_id = t.object_id
        inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
    where fk.referenced_object_id = (select object_id from sys.tables where name = 'customers')
    order by TableWithForeignKey, ForeignKeyColumn

MSDN sys.foreign_key_columns

Upvotes: 11

Related Questions