1. First of those is script for listing all primary keys of some database. Beside name of primary key, it also shows name of column it refers to :
select b.constraint_name,b.column_name, c.nullable
from user_constraints a, user_cons_columns b ,user_tab_columns c
where a.constraint_type = 'P'
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
and b.table_name = c.table_name
and b.column_name = c.column_name
2. Second scrip lists all check constraints of database for certain user:
select a.constraint_name,
b.column_name, a.search_condition
from user_constraints a , user_cons_columns a
where a.constraint_type = 'C'
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
3. Third script lists all foreign constraints of some database:
select a1.constraint_name name_of_constraint,
b1.column_name column,
b2.table_name reference_table,
b2.column_name reference_column, c.nullable
from user_constraints a1 , user_cons_columns b1 ,
user_cons_columns b2, user_tab_columns c
where a1.constraint_type = 'R'
and a1.table_name = b1.table_name
and b1.constraint_name = a1.constraint_name
and b2.constraint_name = a1.r_constraint_name
and c.table_name = a1.table_name
and c.column_name = b1.column_name
3. Third script lists all foreign constraints of some database:
select a1.constraint_name name_of_constraint,
b1.column_name column,
b2.table_name reference_table,
b2.column_name reference_column, c.nullable
from user_constraints a1 , user_cons_columns b1 ,
user_cons_columns b2, user_tab_columns c
where a1.constraint_type = 'R'
and a1.table_name = b1.table_name
and b1.constraint_name = a1.constraint_name
and b2.constraint_name = a1.r_constraint_name
and c.table_name = a1.table_name
and c.column_name = b1.column_name
4. Fourth script lists columns of all tables of some database:
select a.column_name , a.data_type ,
a.data_length , a.data_precision, a.data_scale ,
a.nullable , NVL(b.comments,' ') comments
from user_tab_columns a , user_col_comments b
where a.table_name = b.table_name
and a.column_name = b.column_name
order by a.column_id asc;
5. Fifth script lists all unique keys of some database:
select a.constraint_name,
b.column_name, c.nullable
from user_constraints a ,
user_cons_columns B , user_tab_columns c
where a.constraint_type = 'U'
and a.table_name = b.table_name
and a.table_name= c.table_name
and b.column_name = c.column_name
and a.constraint_name = b.constraint_name
