SQL - How to get the Unique Key's Column Name from Table

Question

I know how to get the columns from a table using the following SQL statement:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE (TABLE_NAME = 'MYTABLENAME')

But how do I just return what the UNIQUE Key's Column name?

Answer 1

select CCU.CONSTRAINT_NAME, CCU.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TC
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as CCU
    on TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG
    and TC.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
    and TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
where TC.CONSTRAINT_CATALOG = 'MyCatalogName'
and TC.CONSTRAINT_SCHEMA = 'MySchemaName'
and TC.TABLE_NAME = 'MyTableName'
and TC.CONSTRAINT_TYPE = 'UNIQUE'

Bear in mind that a table may have multiple unique constraints, each containing multiple columns. You will need to apply some additional logic to select the right one.

UPDATE - based on other comments...

The above query will find all UNIQUE key constraints. However, it will not find PRIMARY KEY constraints, or UNIQUE indexes that were created outside a UNIQUE key constraint.

To find the primary key, replace the last line with:

and TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

Answer 2

Something like this might work (untested):

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
WHERE TC.TABLE_NAME = 'MYTABLENAME'
AND TC.CONSTRAINT_TYPE = 'UNIQUE'