Foreign Key

Hi Friends,
Is there any way to get the table name which is referenced by the
foreign key

for example: consider two table "Staff" and "Department"

Staff with following columns
PK_ID
FK_DepartmentID
Name
Address

Department with following columns
PK_DepartmentID
DeptName

Actually what i need is: Initially i would be having the table name as
"Staff"
from Staff table i need to identify that the column FK_DepartmentID is
a foreign key
and the primary key is in the Department table

i need to traverse from Staff table and identify that FK_DepartmentID
is a primary key in Department table

this has to be accomplished by sql query.... probably this could be
fetched from
Data Dictionary but i couldnt find the relationship between the system
tables.

Thanks
ArunDhaJ

Re: Foreign Key

ArunDhaJ wrote:
> Hi Friends,
> Is there any way to get the table name which is referenced by the
> foreign key
> (..)

(SQL Server 2005)

IMHO the easiest way is to use sys.foreign_keys. You don't need any
other system view. Try this:

USE YOUR_DATABASE; -- remember about current database context

SELECT
OBJECT_NAME(parent_object_id) as table_with_FK,
OBJECT_NAME(referenced_object_id) as referenced_table
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) = 'Staff'


--
Best regards,
Marcin Guzowski
http://guzowski.info