CODEWITHSUNDEEP
sql-servert-sqlprimary-keyalter
DeveloperM
DeveloperM

Reputation: 1239

How to parse a primary key

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.

Upvotes: 1

Views: 141

Answers (1)

TToni
TToni

Reputation: 9391

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'

Upvotes: 1

Related Questions