RE: constraints question

Eugene,

 

How about this:

 

declare

l_pk_name varchar2(100);

in_number number := 1;

 

--===========================================

 

function get_pk_name(in_table_name in varchar2) return varchar2 is

ret_str varchar2(100);

begin

  select constraint_name

  into ret_str

  from user_constraints

  where table_name=in_table_name

  and   constraint_type='P';

  return ret_str;

  exception

    when others then

               return '';

end;

 

--===========================================

 

procedure get_cs_name(in_pk_name in varchar2,in_number in out number) is

l_pk_name  varchar2(100);

l_number NUMBER := in_number;

l_in_number number := 0;

begin

            for c in (select table_name, constraint_name

               from user_constraints

              where r_constraint_name= in_pk_name

                order by table_name) loop

        dbms_output.put_line(lpad('>',l_number*2) || ' ' ||
c.table_name); -- || ' ' || c.constraint_name);

        l_pk_name := get_pk_name(c.table_name);

        if  l_pk_name is not null

        and l_pk_name <> in_pk_name then

           null;

           l_in_number := in_number + 1;

           get_cs_name(l_pk_name,l_in_number);

        end if;

    end loop;

--    l_number := l_number + 1;

end;

 

--===========================================

 

begin

  for c in (select table_name from user_tables order by 1) loop

     dbms_output.put_line(c.table_name);

     l_pk_name := get_pk_name(c.table_name);

     get_cs_name(l_pk_name,in_number);

     dbms_output.put_line ('================================');

  end loop;

end;

/

 

Tom

Other related posts: