CODEWITHSUNDEEP
sqloracle-databaseoracle11g
web_novice118
web_novice118

Reputation: 111

SQL Query for primary key, its data type, and its column name?

As the title suggest, I was curious if somebody knew how to query for a the primary key of a table, its data type and the name of the primary key column. Earlier today, I asked for the primary key column alone and that works well. However, I'm not sure how to pull up its data type and column name as well. Any help would be appreciated.

Thanks!

Upvotes: 1

Views: 4427

Answers (2)

DCookie
DCookie

Reputation: 43543

This should do it:

SELECT ac.constraint_name, acc.column_name, acc.position
     , atc.data_type, atc.data_length
  FROM all_constraints ac JOIN all_cons_columns acc 
                               ON (ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME)
                          JOIN all_tab_cols atc ON (ac.owner = atc.owner AND 
                               ac.table_name = atc.TABLE_NAME AND 
                               acc.COLUMN_NAME = atc.COLUMN_NAME)
 WHERE ac.owner = 'table_owner'
   AND ac.table_name = 'your_table'
   AND ac.constraint_type = 'P'
 ORDER BY acc.position;

Upvotes: 3

ukhardy
ukhardy

Reputation: 2104

Use Query the tables user_tab_columns or all_tab_columns: 

select column_name, data_type, data_length from user_tab_columns 
where table_name = 'MY_TABLE';

select * from all_tab_columns 
where owner = 'THE_SCHEMA_OWNER';

Upvotes: 0

Related Questions