Problem
I have a SQL Server 2000 database with 50 tables. One of the tables is called Person and the primary key in that table is PersonID. I want to find out how many other tables reference the PersonID.
Solution
I used the following T-SQL code to search column names in all tables within the database. It finds all tables with a column named PersonID.
SELECT *
FROM INFORMATION_SCHEMA.columns
WHERE Column_Name = 'PersonID'
Closing
To learn more about the INFORMATION_SCHEMA view and how you can use it to search SQL Server meta-data see the following article: