Developer Forum

Is there a way to find out if a column in a given table is a primary key or
foreign key in another table?

I am using a query to get the tables a particular column name is in, but it
doesn't give this information.

SELECT * FROM sys.columns s
WHERE name = @FieldName AND
OBJECT_NAME(object_id) NOT LIKE '%Old%' AND
OBJECT_NAME(object_id) NOT LIKE 'sys%'

This will give me a list of tables a column belongs to but if there are 10
tables it belongs to - which one is the primary table it is in, such as
StudentID would be the primary key in the Students table but not in the
courses table where it would be a foreign key. I also want to know if it is
neither.

Thanks,

Tom

Try looking in the INFORMATION_SCHEMA views, eg:

SELECT *
FROM [INFORMATION_SCHEMA].constraint_column_usage ccu
JOIN [INFORMATION_SCHEMA].table_constraints cu ON ccu.constraint_name
= cu.constraint_name
WHERE cu.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY')

Stu

try sp_help table_name,

it will get you all the information you need about the table and the fields
inside , constrains , everything

if this don't satisfy you , specify what you need and i'll try to provide it

Regards,

Karim Moussa
http://www.yqcomputer.com/