SQL Server 2005 - how to know if a field is a Primary Key?

Question

If I have the name of a field is there away to know (lookup?) whether this is a PK and if so in which table?

Answer 1

It is in the INFORMATION_SCHEMA.TABLE_CONSTRAINTS

see:

http://sqlserverpedia.com/blog/transact-sql-t-sql/how-do-i-select-the-field-name-primary-key-from-all-tables/

You have to be a little bit carefull. In theory you could have 2 fields with the same name in different tables, and the field being a primary key in one of them.

Answer 2

Use the INFORMATION_SCHEMA views:

SELECT  
    T.TABLE_NAME
FROM  
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T 
    INNER JOIN 
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE K 
        ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME  
WHERE 
    T.CONSTRAINT_TYPE = 'PRIMARY KEY'  
    AND K.COLUMN_NAME = @column_name