Oracle DB query

Question

I need an Oracle DB query where I can get the Primary keys, Data type and the length of an Attribute in BYTES.

Currently I am doing:

SELECT cols.table_name, cols.column_name
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P' 
  AND cons.constraint_name = cols.constraint_name
  AND cons.owner like 'DBP%';

where I am getting the table name, column name which is a primary key. Now I need the data type and the length of the column in BYTES.

Jon Heller · Accepted Answer · 2015-05-04 04:42:41Z

1

Join with all_tab_columns to get the data type and (maximum) length in bytes.

select
    cols.table_name,
    cols.column_name,
    tab_cols.data_type,
    tab_cols.data_length
from all_constraints cons
join all_cons_columns cols
    on cons.owner = cols.owner
    and cons.constraint_name = cols.constraint_name
join all_tab_columns tab_cols
    on cols.owner = tab_cols.owner
    and cols.table_name = tab_cols.table_name
    and cols.column_name = tab_cols.column_name
where cons.constraint_type = 'P'
    and cons.owner like 'DBP%';

You didn't specify but I assume you want the maximum length. If you want the actual used size for the column you could use AVG_COL_LENGTH and multiply it by NUM_ROWS in ALL_TABLES.

answered May 4, 2015 at 4:42

Jon Heller

37k6 gold badges84 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle DB query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related