Query Foreign Key Columns and Tables

I am trying to query the database to get me the foreign key columns and the tables they belong to.

I have:

The name of the table

I need:

The name of the column in thetarget table

The name of the column in thereferenced table

The name of thereferenced table

Any help would be great, thanks

You can use the Management Studio diagramming tool to create it easily but through code it can get complex, the link below will take you in the right directions. Hope this helps.

http://www.sqlservercentral.com/columnists/rlobo/foreignkeys.asp

|||You'd better use system procedure to do this,?which?is?always recommended. For example:

use northwind
go
EXEC sp_helpconstraint Orders

If the result set doesn't fit your need, you can perform query directly on the sysforeighkeys table, but this is not recommended for tons of reasons. For exampe:

DECLARE @.tblName sysname
SET @.tblName='Orders'

SELECT OBJECT_NAME(fkeyid) AS TargetTable,OBJECT_NAME(rkeyid) AS ReferencedTable,
OBJECT_NAME(constid) AS FKName,COL_NAME(fkeyid,fkey) AS TargetColumn,
COL_NAME(rkeyid,rkey) AS ReferencedColumn
FROM sysforeignkeys
WHERE fkeyid=OBJECT_ID(@.tblName)|||

I found the perfect solution this yesterday actually,

I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

|||

BurnChrome:

I found the perfect solution this yesterday actually,

I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

I am glad to see your probelm is resolved.

|||

BurnChrome:

I found the perfect solution this yesterday actually,

I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

I am glad to see your probelm is resolved.