Primary Information

9 Jun 2006 4:05 PM

S Chapman

Given a column id/name and table name/id I need to find out if the
column is a primary key. I have checked all the SYS* tables and could
not find the information I was after. Can you point me in the right
direction please. Thanks.

9 Jun 2006 4:16 PM

Aaron Bertrand [SQL Server MVP]

Sadly, this is not one of the options in the COLUMNPROPERTY() function. You
could write your own function or procedure that does this:

IF EXISTS
(
SELECT
    1
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 T.TABLE_NAME = 'table_name'
    AND K.COLUMN_NAME = 'column_name'
)
    PRINT 'Column is a primary key';
ELSE
    PRINT 'Column is not a primary key';

Show quote

"S Chapman" <s_chapma***@hotmail.co.uk> wrote in message
news:1149869140.156553.134430@i40g2000cwc.googlegroups.com...
>
> Given a column id/name and table name/id I need to find out if the
> column is a primary key. I have checked all the SYS* tables and could
> not find the information I was after. Can you point me in the right
> direction please. Thanks.
>

9 Jun 2006 4:37 PM

S Chapman

Thank you very much. INFORMATION_SCHEMA is an eye-opener.

Aaron Bertrand [SQL Server MVP] wrote:
Show quote

> Sadly, this is not one of the options in the COLUMNPROPERTY() function. You
> could write your own function or procedure that does this:
>
> IF EXISTS
> (
> SELECT
>     1
> 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 T.TABLE_NAME = 'table_name'
>     AND K.COLUMN_NAME = 'column_name'
> )
>     PRINT 'Column is a primary key';
> ELSE
>     PRINT 'Column is not a primary key';
>
>
> "S Chapman" <s_chapma***@hotmail.co.uk> wrote in message
> news:1149869140.156553.134430@i40g2000cwc.googlegroups.com...
> >
> > Given a column id/name and table name/id I need to find out if the
> > column is a primary key. I have checked all the SYS* tables and could
> > not find the information I was after. Can you point me in the right
> > direction please. Thanks.
> >