Oracle dba_tab_cols query -

hi possible retrieve primary key , unique key using dba_tab_cols query?

is there query allows me retrieve of following fields?

• column name
• data type
• primary key
• null/not null
• unique key
• default value
• extra

both primary , unique keys can span more 1 column, wouldn't belong in dba_tab_columns. you'd need @ dba_constraints , dba_cons_columns information.

this starting point, maybe:

select owner, table_name, column_name, data_type, primary_key,   nullable, unique_key, data_default (   select dtc.owner, dtc.table_name, dtc.column_id, dtc.column_name,     dtc.data_type, dtc.nullable, dtc.data_default,     case when dc.constraint_type = 'p' , dcc.column_name = dtc.column_name       dc.constraint_name end primary_key,     case when dc.constraint_type = 'u' , dcc.column_name = dtc.column_name       dc.constraint_name end unique_key,     row_number() on (partition dtc.owner, dtc.table_name, dtc.column_id       order null) rn   dba_tab_columns dtc   left join dba_constraints dc   on dc.owner = dtc.owner   , dc.table_name = dtc.table_name   , dc.constraint_type in ('p', 'u')   left join dba_cons_columns dcc   on dcc.owner = dc.owner   , dcc.constraint_name = dc.constraint_name   , dcc.table_name = dc.table_name   , dcc.column_name = dtc.column_name   dtc.owner = '<owner>'   , dtc.table_name = '<table_name>' ) rn = 1 order owner, table_name, column_id; 

i've done subquery generates row_number value because you'd duplicates table more 1 constraint; , because want default value, long (column data_default), can't use distinct or group by. feels bit inelegant, i'm sure can work on need.

it's possible have check constraint replicates not null version, though isn't advisable. , unique index won't show unique constraint, might want 1 of too, via dba_indexes , dba_ind_columns. index used unique constrain appear in both, though.

you @ dbms_metadata.get_ddl information too, depending on intend it. i'm not sure why useful, other try recreate schema elsewhere, , there better tools doing that.