SQL Queries - Common SQL Queries - How to retrieve the primary key of a table

Purpose of Query

The query retrieves the primary key of a table

Applicable CAST Version

Release	Yes/No
8.3.x
8.2.x
8.1.x
8.0.x

Applicable RDBMS

RDBMS	Yes/No
Oracle Server
Microsoft SQL Server
CSS2

Query for CSS

Query result example

SELECT constraint_name,
       table_name,
       column_name,
       ordinal_position
FROM   information_schema.key_column_usage
WHERE  constraint_schema = 'schema_name'
       AND table_name = 'table_name'

You need to replace <table_name> with the name of the table, and <schema_name> with the name of the schema hosting the table

SELECT constraint_name, 
       table_name, 
       column_name, 
       ordinal_position 
FROM   information_schema.key_column_usage 
WHERE  constraint_schema = 'my_local_schema' 
       AND table_name = 'objdsc'

Query result interpretation

The query returns the primary key name and the table name, if no primary key exists on the table then the query returns no rows

Query for Oracle

Query result example

Query result interpretation

Query for SQL Server

Query result example

Query result interpretation

Notes/comments

Related Pages