Oracle constraints info user wise ..
set serveroutput on
DECLARE
constraint_text long;
c_name varchar2(100);
c_type varchar2(100);
c_owner varchar2(100);
c_table_name varchar2(100);
cursor CONSTRAINTS is SELECT owner, table_name, search_condition,constraint_name, constraint_type
FROM dba_constraints where OWNER in ('SCOTT');
BEGIN
OPEN CONSTRAINTS;
LOOP
FETCH CONSTRAINTS INTO c_owner, c_table_name, constraint_text, c_name, c_type;
exit when CONSTRAINTS%NOTFOUND;
IF instr(upper(constraint_text),'IS NOT NULL') = 0 or constraint_text is null THEN
dbms_output.put_line ('==============================================');
dbms_output.put_line ('Table Name = '||c_owner||'.'||c_table_name);
dbms_output.put_line ('Constraint Name = '||c_name);
dbms_output.put_line ('Constraint Name = '||c_name);
dbms_output.put_line ('Constraint Type = '||c_type);
dbms_output.put_line ('Search Condition = '||constraint_text);
END IF;
END LOOP;
CLOSE CONSTRAINTS;
dbms_output.put_line ('==============================================');
END;
/
--- ========================================================
---With column
set serveroutput on
DECLARE
constraint_text long;
c_name varchar2(100);
c_type varchar2(100);
c_owner varchar2(100);
c_table_name varchar2(100);
b_column_name varchar2(100);
cursor CONSTRAINTS is SELECT c.owner,c.table_name, c.search_condition,c.constraint_name, c.constraint_type,b.column_name FROM dba_constraints c,dba_cons_columns b
where 1=1
and c.constraint_name = b.constraint_name
AND c.owner = b.owner
and c.TABLE_NAME=b.TABLE_NAME
and c.OWNER in ('SCOTT')
order by 2;
BEGIN
OPEN CONSTRAINTS;
LOOP
FETCH CONSTRAINTS INTO c_owner, c_table_name, constraint_text, c_name, c_type,b_column_name ;
exit when CONSTRAINTS%NOTFOUND;
IF instr(upper(constraint_text),'IS NOT NULL') = 0 or constraint_text is null THEN
dbms_output.put_line ('==============================================');
dbms_output.put_line ('Table Name = '||c_owner||'.'||c_table_name);
dbms_output.put_line ('column Name = '||b_column_name);
dbms_output.put_line ('Constraint Name = '||c_name);
dbms_output.put_line ('Constraint Type = '||c_type);
dbms_output.put_line ('Search Condition = '||constraint_text);
END IF;
END LOOP;
CLOSE CONSTRAINTS;
dbms_output.put_line ('==============================================');
END;
/
==============================================
Table Name = SCOTT.DEPT
column Name = DEPTNO
Constraint Name = PK_DEPT
Constraint Type = P
Search Condition =
==============================================
Table Name = SCOTT.EMP
column Name = EMPNO
Constraint Name = PK_EMP
Constraint Type = P
Search Condition =
==============================================
Table Name = SCOTT.EMP
column Name = DEPTNO
Constraint Name = FK_DEPTNO
Constraint Type = R
Search Condition =
==============================================
Table Name = SCOTT.PRIKEY_TEST
column Name = ID
Constraint Name = PK_UNIQUE_TEST
Constraint Type = P
Search Condition =
==============================================
PL/SQL procedure successfully completed.
No comments:
Post a Comment