Oracle List of constraints owned by the current user

constraint list
List of constraints owned by the current user
List constraints for a table owned by the current user




SQL> CREATE OR REPLACE TYPE db_constraint_ot AS OBJECT
( owner VARCHAR2(30)
, constraint_name VARCHAR2(30)
, constraint_type VARCHAR2(1)
, table_name VARCHAR2(30)
, search_condition VARCHAR2(32767)
, r_owner VARCHAR2(30)
, r_constraint_name VARCHAR2(30)
, delete_rule VARCHAR2(9)
, status VARCHAR2(8)
, deferrable VARCHAR2(14)
, deferred VARCHAR2(9)
, validated VARCHAR2(13)
, generated VARCHAR2(14)
, bad VARCHAR2(3)
, rely VARCHAR2(4)
, last_change DATE
, index_owner VARCHAR2(30)
, index_name VARCHAR2(30)
, invalid VARCHAR2(7)
, view_related VARCHAR2(14) )
/

Type created.

SQL> CREATE OR REPLACE TYPE db_constraint_tt AS TABLE OF db_constraint_ot;
2 /

Type created.

SQL> CREATE OR REPLACE TYPE varchar2_tt AS TABLE OF VARCHAR2(4000) ;
2 /

Type created.







def table = &1
set term off
store set sqlplus_settings.sql replace
TTITLE OFF
var tabname VARCHAR2(30)
var results REFCURSOR
exec :tabname := UPPER('&table')
col table new_value table
SELECT UPPER(:tabname) AS "TABLE" FROM dual;
set def on term on autoprint off feed off lines 110

col type hea "Type"
col constraint_name hea "Constraint name"
col table_name hea "Table name"
col table_owner format a20 hea "Owner"
col status hea "Status"
col deferrable hea "Deferrable?"
col deferred hea "Deferred?"

col search_condition format a60 word hea "Definition"

DECLARE
v_table_exists VARCHAR2(1) := 'Y';
v_constraints DB_CONSTRAINT_TT := DB_CONSTRAINT_TT();

CURSOR c_constraints (cp_tablename all_constraints.table_name%TYPE)
IS
SELECT c.owner
, CASE
WHEN c.generated = 'GENERATED NAME' AND c.constraint_name LIKE 'SYS\_%' ESCAPE '_'
THEN '[' || c.constraint_name || ']'
ELSE c.constraint_name
END AS constraint_name
, c.constraint_type
, c.table_name
, c.search_condition
, TO_CHAR(NULL) AS column_list
, DECODE(c.constraint_type,
'R', 'Foreign key (%COLS%) to ' || r.table_name
|| ' (' || c.r_constraint_name
|| ')'
|| DECODE(c.delete_rule, 'CASCADE', ', ' || c.delete_rule),
'P', 'Primary key (%COLS%)',
'U', 'Unique key (%COLS%)'
) key_description
, CAST
( MULTISET
( SELECT column_name
FROM all_cons_columns
WHERE owner = c.owner
AND constraint_name = c.constraint_name
ORDER BY position
) AS VARCHAR2_TT ) key_columns
, c.r_owner
, c.r_constraint_name
, c.delete_rule
, c.status
, c.deferrable
, c.deferred
, c.validated
, c.generated
, c.bad
, c.rely
, c.last_change
, c.index_owner
, c.index_name
, c.invalid
, c.view_related
FROM all_constraints c
, all_constraints r
WHERE c.table_name = cp_tablename
AND c.owner = USER
AND r.constraint_name (+)= c.r_constraint_name
AND r.owner (+)= c.r_owner;
BEGIN
FOR r IN c_constraints(:tabname)
LOOP
v_constraints.EXTEND;

IF r.key_columns.COUNT > 0 THEN
FOR i IN r.key_columns.FIRST..r.key_columns.LAST LOOP
r.column_list := r.column_list || r.key_columns(i) || ', ';
END LOOP;
r.column_list := RTRIM(r.column_list,', ');
END IF;

r.key_description := REPLACE(r.key_description,'%COLS%', r.column_list);

v_constraints(c_constraints%ROWCOUNT) :=
DB_CONSTRAINT_OT
( r.owner
, r.constraint_name
, r.constraint_type
, r.table_name
, NVL(r.search_condition,r.key_description)
, r.r_owner
, r.r_constraint_name
, r.delete_rule
, r.status
, r.deferrable
, r.deferred
, r.validated
, r.generated
, r.bad
, r.rely
, r.last_change
, r.index_owner
, r.index_name
, r.invalid
, r.view_related );
END LOOP;

IF v_constraints.COUNT = 0 THEN
-- Nothing in ALL_CONSTRAINTS - check table exists:
SELECT MIN('N') INTO v_table_exists
FROM dual
WHERE NOT EXISTS
( SELECT 1 FROM all_tables
WHERE table_name = :tabname
AND owner = USER );

IF v_table_exists = 'N' THEN
DBMS_OUTPUT.PUT_LINE('No such table "' || :tabname || '"');
END IF;
END IF;

OPEN :results FOR
SELECT CASE
WHEN search_condition LIKE '"%" IS NOT NULL' THEN 'Not Null'
WHEN constraint_type = 'C' THEN 'Check'
WHEN constraint_type = 'U' THEN 'Unique'
WHEN constraint_type = 'P' THEN 'Primary'
WHEN constraint_type = 'R' THEN 'FK'
ELSE constraint_type
END AS type
, constraint_name
, search_condition
, status
FROM TABLE(v_constraints)
ORDER BY
CASE
WHEN constraint_type = 'P' THEN '1'
WHEN constraint_type = 'U' THEN '2'
WHEN constraint_type = 'R' THEN '3'
WHEN constraint_type LIKE '"%" IS NOT NULL' THEN '4'
WHEN constraint_type = 'C' THEN '5'
ELSE constraint_type
END
, constraint_name;
END;
/

set term on

print :results





TTITLE ON LEFT 'Tables referencing &table:' SKIP1

SELECT -- r_owner AS table_owner
table_name
, constraint_name
, status
, deferrable
, deferred
FROM all_constraints
WHERE constraint_type = 'R'
AND r_owner = USER
AND r_constraint_name IN
( SELECT constraint_name
FROM user_constraints
WHERE table_name = :tabname
AND constraint_type IN ('P','U') );

prompt
TTITLE ""
TTITLE OFF
@sqlplus_settings.sql




SQL> create table anuj1( x number primary key ) ;


test this script


SQL> @cons
Enter value for 1: ANUJ1

Type Constraint name Definition Status
-------- ------------------------------ ------------------------------------------------------------ --------
Primary SYS_C0021021 Primary key (X) ENABLED





SQL> create table anuj2( n number, CONSTRAINT anuj2_fk FOREIGN KEY(n) REFERENCES anuj1(x) );

SQL> desc anuj2
Name Null? Type
----------------------------------------------------------- -------- N NUMBER


SQL> @cons
Enter value for 1: anuj1

Type Constraint name Definition Status
-------- ------------------------------ ------------------------------------------------------------ --------
Primary SYS_C0021021 Primary key (X) ENABLED

Tables referencing ANUJ1:
Table name Constraint name Status Deferrable? Deferred?
------------------------------ ------------------------------ -------- -------------- ---------
ANUJ2 ANUJ2_FK ENABLED NOT DEFERRABLE IMMEDIATE





save_sqlplus_settings.sql
set termout off
store set sqlplus_settings replace
clear breaks
clear columns
clear computes
set feedback off
set verify off
set termout on
set define "&"




restore_sqlplus_settings.sql
set termout off
@sqlplus_settings
clear breaks
clear columns
clear computes
set termout on
============================


or


SQL> col table_name format a32
col columns format a40
set lines 140
set pages 200
select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
order by table_name
/


TABLE_NAME CONSTRAINT_NAME COLUMNS
-------------------------------- ------------------------------ ----------------------------------------
EMP FK_DEPTNO DEPTNO