Question about primary key!

Question

Post reply

Question about primary key!

andersonrj18

SSC Veteran

Points: 264
More actions
September 8, 2009 at 3:30 pm

#96116

Does anyone know how to identify composite primary keys in sql server view?

I mean, a table which contains this detail, for example:

TableA -> Primary Key (Id,name)

TableB -> PrimaryKey (id)

I'm going to pass the name of the column, (ID) and my query would return

"Table with unique primary key for column ID: TableA",

because the ID in TableB is composite.

I've tried do a query using sys.columns, sys.tables and sys.key_column_usage, but the

query always return the bothm TableA and TableB, I just need to filter this.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150457 More actions · Answer 1

There is more than one way. Here's one:



SELECT  N'Table with unique primary key for column ID: ' +

        SCHEMA_NAME(T.[schema_id]) + N'.' +

        OBJECT_NAME(T.[object_id])

FROM    sys.key_constraints KC

JOIN    sys.index_columns IC

        ON  IC.[object_id] = KC.parent_object_id

        AND IC.index_id = KC.unique_index_id

JOIN    sys.columns C

        ON  C.[object_id] = KC.parent_object_id

        AND C.column_id = IC.column_id

JOIN    sys.tables T ON T.[object_id] = KC.parent_object_id

WHERE   C.name = N'ID'

AND     KC.[type] = N'PK'

AND     INDEX_COL(SCHEMA_NAME(T.[schema_id]) + N'.' + OBJECT_NAME(T.[object_id]), IC.index_id, 2) IS NULL;