set pagesize 300
set linesize 130
set head on
set echo off
accept tab prompt “Enter The Table Name -> ”
select b.constraint_name “Cons Name”, b.status,
decode(b.constraint_type, ‘P’, ‘PRIMARY’, ‘C’, ‘CHECK’, ‘U’, ‘UNIQUE’, ‘R’, ‘FOREIGN KEY’) “Cons Type”,
substr(a.column_name, 1, 20) “Column Name”,
decode(b.constraint_type, ‘C’, ‘ MUST NOT BE NULL’ , ‘ Position : ‘) ||
decode(b.constraint_type, ‘C’, null, to_char(a.position) ) ||
decode (b.constraint_type, ‘R’, ‘ REFERENCES ‘, NULL) ||
decode (b.constraint_type, ‘R’, c.xparent , NULL) “Cons Description”
from user_cons_columns a, user_constraints b,
(select parent.table_name xparent, child.table_name xchild, child.constraint_name xcons
from user_constraints parent, user_constraints child
where parent.constraint_name = child.r_constraint_name) c
where b.table_name = upper(‘&TAB’)
and a.constraint_name = b.constraint_name
and a.constraint_name = c.xcons(+)
order by b.constraint_name, a.position;
set head off
select ‘AND THIS TABLE &tab IS PARENT TABLE OF FOLLOWING TABLES’ from dual;
set head on
select a.table_name parent, b.table_name child, b.constraint_name “Child Cons Name”, b.status
from user_constraints a, user_constraints b
where a.constraint_name = b.r_constraint_name
and a.table_name = upper(‘&tab’);
set head on