Finding foreign key references to a table.

At some point you'll find yourself wanting to modify or change the way a table is structured. If your database uses FK (foreign key) constraints it is sometime a pain to find all the FKs referencing the table. Microsoft’s management tool will list dependant objects but drilling down to which tables and columns reference which column on the table you want to modify can take some time.

Here is a little procedure to list out FKs on a target table:
--
-- Find all foreign keys references to a particualr table
-- D Winters
--
declare @targetTab sysname

set @targetTab = 'employee'

select t.name as TableWithForeignKey, object_name(constraint_object_id) as ForeignKeyConstraint,
c.name as ForeignKeyColumn, fk.constraint_column_id as FK_PartNo ,
COL_NAME(referenced_object_id , referenced_column_id ) as References_Column
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 = @targetTab)
order by TableWithForeignKey, FK_PartNo