Various Oracle Data Dictionary Queries

adapted from here.



TABLES

    select distinct lower(table_name) as table_name
      from user_tab_columns
  order by 1

SEQUENCES

    select lower(sequence_name) as sequence_name,
           min_value,
           max_value,
           increment_by,
           cycle_flag,
           order_flag,
           cache_size
      from user_sequences
  order by 1


FOREIGN KEYS

    select ucc.constraint_name,
           ucc.column_name,
           fc.table_name
      from user_cons_columns ucc,
           user_constraints fc,
           user_constraints uc
     where uc.constraint_type = 'R' and
           uc.constraint_name = ucc.constraint_name and
           fc.constraint_name = uc.r_constraint_name and
           uc.table_name='%s'
  order by 1, 2


DESC

    select column_name as name,
           data_type as type,
           char_length as length,
           nullable,
           data_default as "default"
      from user_tab_columns
     where table_name='%s'
  order by column_name


CONSTRAINTS

    select ucc.constraint_name,
           ucc.column_name,
           uc.constraint_type,
           uc.search_condition
      from user_constraints uc,
           user_cons_columns ucc
     where uc.constraint_name = ucc.constraint_name and
           uc.table_name='%s' and
           uc.constraint_type = 'C'
  order by ucc.constraint_name, ucc.position

TRIGGERS

    select trigger_name,
          trigger_type,
          triggering_event,
          table_name,
          description,
          trigger_body
     from user_triggers
 order by 1

LIST_INDEX

    select case
             when constraint_type = 'P' then 'PRIMARY KEY'
             else ' ' end as index_type,
           ui.index_name,
           ui.uniqueness,
           uic.column_name,
           uic.column_position,
           uic.descend
      from user_indexes ui
           join user_ind_columns uic on uic.index_name = ui.index_name
           left join user_constraints
             on user_constraints.constraint_name = ui.index_name and
             user_constraints.constraint_type = 'P'
     where ui.table_name = '%s'
  order by constraint_type, uic.column_position;

INDEX

    select case
             when constraint_type = 'P' then 'PRIMARY KEY'
             else ' ' end as index_type,
           ui.table_name,
           ui.index_name,
           ui.uniqueness,
           uic.column_name,
           uic.column_position,
           uic.descend
      from user_indexes ui
             join user_ind_columns uic on uic.index_name = ui.index_name
             left join user_constraints
               on user_constraints.constraint_name = ui.index_name and
               user_constraints.constraint_type = 'P'
     where ui.index_name = '%s'
  order by constraint_type, uic.column_position;