Which system table tells you if a column is primary key?

24 Mar 2006 2:41 PM

Charlie@CBFC

Thanks
Charlie

24 Mar 2006 3:22 PM

ML

Look up OBJECTPROPERTY system function in Books Online, or here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_708p.asp

ML

---
http://milambda.blogspot.com/

25 Mar 2006 1:26 PM

Dan Guzman

Here's an example that retrieves primary key column info using the
INFORMATION_SCHEMA views:

SELECT
    pk.TABLE_SCHEMA AS TableSchema,
    pk.TABLE_NAME AS TableName,
    pk_col.COLUMN_NAME AS ColumnName,
    pk_col.ORDINAL_POSITION AS OrdinalPosition
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk_col ON
    pk_col.CONSTRAINT_SCHEMA = pk.CONSTRAINT_SCHEMA AND
    pk_col.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
WHERE
    pk.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
    pk.TABLE_SCHEMA,
    pk.TABLE_NAME,
    pk_col.ORDINAL_POSITION

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Charlie@CBFC" <fineblu***@verizon.net> wrote in message
news:%23mJctE1TGHA.4452@TK2MSFTNGP12.phx.gbl...
> Thanks
> Charlie
>

27 Mar 2006 6:38 AM

Razvan Socol

(reposting using the Microsoft's site, since the Google Groups post was not
found here)

Hi, Charlie

Considering that the primary key of a table can be composed of more
than one column, the correct question would be "if a column is PART OF
THE primary key".

To find out the columns which are part of the primary key of a table,
you can use:
a) the INFORMATION_SCHEMA.KEY_COLUMN_USAGE and
INFORMATION_SCHEMA.TABLE_CONSTRAINTS views
or:
b) the sysindexkeys, sysindexes and sysobjects system tables.

For more informations, see this response for a similar question:
http://groups.google.com/group/comp.databases.ms-sqlserver/msg/f329db...

Razvan