Find all references to a table column in SQL Server 2008

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

When I need to analyze impact due to table schema change, I use following steps for analysis. This may not be complete but helpful!

1. Sp_help [TableName] : This helps me to find all FOREIGN KEY constraints References. I can also find any other tables referencing this table as FOREIGN Key.
2. Sp_depends [TableName]: This helps to find Procedures, Functions, and Views using this table.
3. Brute force method: I use different System tables and functions to check for specific keyword in SQL Jobs, modules, etc

FYI: Sp_help [TableName] Sp_depends [TableName]

select top 10 * from msdb.dbo.sysjobsteps where command like '%%'

SELECT top 10 Name, OBJECT_DEFINITION(OBJECT_ID)
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%%' 

SELECT top 10 OBJECT_NAME(object_id), *
FROM sys.sql_modules
WHERE definition LIKE '%%'

You can try using a tool such as ApexSQL Search. It searches for object names but it also searches for a list of dependent objects even if columns in dependent tables/views are named differently.

Other solution is to use system views and/or system functions to get the data you need. Suggestion is to use sys.foreign_keys, sys.objects, sys.all_columns to get the data you need.