Nifty Pulp Magazine Cover Generator

http://thrilling-tales.webomator.com/derange-o-lab/pulp-o-mizer/pulp-o-mizer.html

Perfect for all your software projects!

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;

Interesting way to generate some Oracle sample tables

A lot of times you need to create a sample table with a primary and foreign key, populated with some kind of name-like field.  Here's an interesting way to do it.

create table emp_tab as
select
  rownum empno,
  object_name ename,
  mod(rownum, 10) + 1 deptno,
  rownum * 100 sal
from all_objects
where rownum < 100;



Now you can do the same thing, for the foreign key.

create table dept_tab as
select
  rownum deptno,
  'd_' || rownum  deptname
from all_objects
where rownum <= 10;




mh@templar> select * from emp_tab;
empno ename                       deptno sal  
----- -----                       ------ ---  
    1 DUAL                             2  100 
    2 DUAL                             3  200 
    3 SYSTEM_PRIVILEGE_MAP             4  300 
    4 SYSTEM_PRIVILEGE_MAP             5  400 
    5 TABLE_PRIVILEGE_MAP              6  500 
    6 TABLE_PRIVILEGE_MAP              7  600