How to parse a primary key

Question

Using TSQL I want to look at a table's primary key and if it isn't up to date, alter the table accordingly. The PK has 3 composite columns; someone added one on one server and a job died on another server. I know how to test for the existence of a PK, just not how to look at its component columns.

Answer 1

This should give you the necessary information:

select ccu.* 
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
    ON 
        tc.CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG AND 
        tc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA AND
        tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME AND
        tc.CONSTRAINT_TYPE = 'PRIMARY KEY'