given a foreign key, how do i find all tables that use that particular key?

Question

i have a column (myColumn) that serves as the primary key of a table (tableA). i've noticed that 2 or 3 tables reference this myColumn as foreign key. how do i detect all tables that use and reference myColumn?

im guessing that more than 3 tables use myColumn because when i tried updating it like this

UPDATE tableA SET myColumn = 1 WHERE myColumn = 1

6 rows were updated.

it was earlier suggested to me to use

sp_helpconstraint('your_table_name')

but i then found out that this does not give the complete information that i need.

any other suggestions?

duplicate: stackoverflow.com/questions/831589/…

Mitch Wheat
– Mitch Wheat

2012-01-30 05:40:33 +00:00
Commented Jan 30, 2012 at 5:40 — Mitch Wheat
– Mitch Wheat, Commented Jan 30, 2012 at 5:40

marc_s · Accepted Answer · 2012-01-30 06:02:36Z

Try this - this is the more up-to-date, SQL Server 2005 and newer version of my original answer that Mitch linked to (that was for SQL Server 2000):

SELECT ConstraintName = fk.name, TableName = t.name, ColumnName = c.name FROM sys.foreign_keys fk INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id INNER JOIN sys.tables tref ON fk.referenced_object_id = tref.object_id INNER JOIN sys.columns cref ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id WHERE tref.Name = 'Person' AND cref.Name = 'OID'

It uses the system catalog views sys.foreign_keys and sys.foreign_key_columns to find out which tables/columns reference that table and column you're interested in.

You just basically type in the table name and the column name in the WHERE clause - and you get your list of other tables/columns referencing that table/column

Alex · Accepted Answer · 2012-01-30 05:56:17Z

If you install Redgate's free sql search tool you can easily search your database schema for strings such as tables and column names.

http://www.red-gate.com/products/sql-development/sql-search/

There are other ways to search the schema using system tables but I highly recommend this tool. It will become part of your everyday workflow if you are constantly reverse engineering large databases.

Collectives™ on Stack Overflow

given a foreign key, how do i find all tables that use that particular key?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related