Sunday, April 18, 2010

dba_constraints/ user_constraints/ all_constraints queries

Simply list of constranits along with the constraint_type you can achieve querying from dba_constraints/ user_constraints/ all_constraints.

SQL> select owner, constraint_name, constraint_type from dba_constraintswhere owner='&Owner_name' and table_name='&Tab_name';

Output:

In the column constraint_type there may have values C,P,U,R,V and O which means,
1) C :check constraint on a table
2) P :primary key
3) U :unique key
4) R :referential integrity
5) V :with check option, on a view
6) O :with read only, on a view

You can get constraints along with their columns and position by querying from DBA_CONS_COLUMNS/ USER_CONS_COLUMNS/ ALL_CONS_COLUMNS.

SQL> col owner for a10
col constraint_name for a27
col table_name for a25
col column_name for a23
set pages 100
SQL> select * from dba_cons_columns where table_name='&Tab_name' and owner='&Owner_name' order by constraint_name, position;

By joining both two views you can get a list of constraints , their type, column_name, column position and their reference constraint name, reference table name in the constraint by,

SQL> col r_owner for a10
SQL> Select c.constraint_name, cc.column_name, c.r_owner,c.r_constraint_name,c.constraint_type,cc.position, r.table_namefrom dba_constraints c JOIN dba_cons_columns cc ON(c.table_name=cc.table_name AND c.owner=cc.ownerAND c.constraint_name=cc.constraint_name) LEFT JOIN dba_constraints rON(c.r_constraint_name=r.constraint_name AND r.constraint_type in ('P','U') ) where c.table_name='&Tab_name' and c.owner='&Owner_name' order byconstraint_name, position;

Thanks.

1 comment:

Anonymous said...

A huge dick in my pussy,any warm wet tounge up my own arse and cum as
well as pussy juice all over me. Fuck, ozzy

Look into my web-site :: hcg injections