CODEWITHSUNDEEP
sqlsql-server
Alex
Alex

Reputation: 2111

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

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?

Upvotes: 1

Views: 99

Answers (2)

David M
David M

Reputation: 72860

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

Upvotes: 3

Shiraz Bhaiji
Shiraz Bhaiji

Reputation: 65391

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.

Upvotes: 3

Related Questions