Oracle DBA Script : Return Information about constraint

Script prompts for a constraint name then it returns information about the constraint.

Code:

set lines 120
set serveroutput on size 100000
set scan on
clear buffer
set verify off
set feedback off
declare

l_constraint_name varchar2(30);
l_constraint_type varchar2(1);
l_table_name varchar2(30);
l_search_condition long;
l_related_constraint varchar2(30);
l_related_table varchar2(30);
l_rcolumn varchar2(30);
l_column varchar2(30);
l_query varchar2(400);
l_column_count integer;

cursor lc_constraint (p_constraint_name varchar2) is
select *
from dba_constraints
where constraint_name = p_constraint_name;

cursor lc_related_constraint(p_r_constraint_name varchar2)
is

select table_name from dba_constraints
where constraint_name = p_r_constraint_name;

cursor lc_columns (p_constraint_name varchar2) is
select column_name from dba_cons_columns
where constraint_name = p_constraint_name;
begin
dbms_output.enable(1000000);
for rec_constraint in
lc_constraint(upper('&constraint_name'))
loop
l_constraint_type := null;
l_table_name := null;
l_related_constraint := null;
l_search_condition := null;
l_constraint_type :=
rec_constraint.constraint_type;
l_table_name :=
rtrim(rec_constraint.table_name);
l_related_constraint :=
rec_constraint.r_constraint_name;
l_search_condition :=
rec_constraint.search_condition;
l_constraint_name :=
rec_constraint.constraint_name;
l_column_count := 0;
dbms_output.put_line(
);
if l_constraint_type = 'P' then
dbms_output.put_line(l_constraint_name || ' is a primary key constraint on table ' || l_table_name);
open lc_columns(l_constraint_name);
fetch lc_columns into l_column;
dbms_output.put_line('Column ' ||
l_column);
close lc_columns;
end if;
if l_constraint_type = 'U' then
dbms_output.put_line(l_constraint_name || ' is a unique constraint on table ' || l_table_name);
for rec_columns in
lc_columns(l_constraint_name)
loop
dbms_output.put_line('Column ' ||
rec_columns.column_name);
end loop;
end if;
if l_constraint_type = 'C' then
dbms_output.put_line(l_constraint_na
me || ' is a check constraint ' ||
rtrim(l_table_name) || ' where '||
l_search_condition);
end if;
if l_constraint_type = 'R' then
open
lc_related_constraint(l_related_constr
aint);
fetch lc_related_constraint into
l_related_table;
dbms_output.put_line('Table
'||l_table_name ||' referential
constraint '|| l_related_constraint ||'
failed because ');
l_query := ' select ' ;
l_column_count := 0;
open lc_columns(l_constraint_name);
fetch lc_columns into l_column;
l_query := l_query || ' ' || rtrim(l_column);
if l_column_count > 1 then
l_query := l_query || ',';
end if;
l_column_count := l_column_count +1;
dbms_output.put_line('Column'||l_column);
close lc_columns;
l_query := l_query || 'from'|| l_table_name || ' minus select ' ;
l_column_count := 0;
dbms_output.put_line('Table'|| l_related_table||' is missing related values ');
open
lc_columns(l_related_constraint);
fetch lc_columns into l_rcolumn;
l_query := l_query || ' ' ||
rtrim(l_rcolumn);
if l_column_count > 1 then
l_query := l_query || ',';
end if;
l_column_count := l_column_count +1;
dbms_output.put_line('Column'||l_rcolumn);
close lc_columns;
l_query := l_query || ' from ' || rtrim(l_related_table) ||';';
dbms_output.put_line(l_query);
close lc_related_constraint;
end if;
if l_constraint_type not in ('C','P','U','R') then
dbms_output.put_line(l_table_name||'
has a unknown constraint type');
end if;
end loop;
end;
/
set feedback on