CODEWITHSUNDEEP
sqloracle-databaseplsqlsqlplusidentity-column
Prasanth Pennepalli
Prasanth Pennepalli

Reputation: 1058

List of all identity columns in Oracle

How do you get a list of all identity columns in Oracle SQL?

Upvotes: 0

Views: 5496

Answers (4)

fsbflavio
fsbflavio

Reputation: 874

select * from user_tab_identity_cols;

Upvotes: 1

William Robertson
William Robertson

Reputation: 16001

Query USER|ALL|DBA|CDB_TAB_IDENTITY_COLS for technical details about the identity settings.

Query USER|ALL|DBA|CDB_TAB_COLUMNS where IDENTITY_COLUMN = 'YES' as suggested in Prasanth's answer for general information about the columns.

Upvotes: 2

Sheetal Nanda
Sheetal Nanda

Reputation: 28

There are no identity columns in Oracle as in SQL server. There is a concept of Primary Key in Oracle. You can find list of primary keys for all tables in DB using below query:

SELECT a.table_name,a.column_name
FROM all_cons_columns a, all_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'P';

Hope this helps:)

Upvotes: -4

Prasanth Pennepalli
Prasanth Pennepalli

Reputation: 1058

SELECT table_name, column_name FROM all_tab_columns WHERE identity_column = 'YES';

Upvotes: 3

Related Questions