3

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?

user582485
  • 509
  • 3
  • 13
  • 23
  • duplicate: http://stackoverflow.com/questions/831589/query-to-get-all-foreign-key-constraints-in-sql-server-2000 – Mitch Wheat Jan 30 '12 at 05:40

2 Answers2

3

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
1

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.

Alex
  • 2,350
  • 2
  • 20
  • 17