Marshall's Weblog

Have you ever needed a list of the foriegn keys of a table but didn’t want to parse out the contents of sp_help? Well I did and here is what I came up with

create proc sp_GetKeysForTable
@objname varchar(100)
as
select @objname ForiegnKeyTable, col_name(r.fkeyid, fk.fkey) ForiegnKeyColumn,
object_name(r.rkeyid) PrimaryKeyTable, col_name(r.rkeyid, fk.rkey) PrimaryKeyTable
from sysreferences r join sysforeignkeys fk on r.constid = fk.constid
join sysobjects s on r.constid = s.id and parent_obj = object_id(@objname)

It takes in a table name and returns all of the foriegn keys on the table as well as the table and column it references.