A script for those who don't use Designer or modeling
or didn't reverse eng. existing DBs into a Design
tool. You get pretty good names automatically when you
translate your models into schemas along with many
other benefits obviating the need for such scripts.
Also, if you are relying on constraint names for your
error correction may be you should also look at using
Exceptions table(s) for capturing constraint
violations and reporting details from a join of the
Exceptions table with all_costraints to report what is
meaningful about the constraint. You will be woefully
limited to construct or guess all the details from a
30 char long name with FK_ prefixed or suffixed.
Designer gives you fairly reasonable names but if you
are generating a report for violations in your code
you need to look beyond the name. You can report all
the meaningful details from one of the constraint
views.
I am not casting any aspersions here but many DBAs I
have worked with tend to take a abstruse and techie
route to everyday problems. In most environments you
should have rigourous integrity checking and mostly
such violations should be reported to both IS and
concerned business managers to address underlying
issues. I digress but the key thing is to have
business terms used for entities and attributes and
have a solid catalog of meta data. The schema terms
automatically follow these business terms upon data
transformation. When you generate error reports from
such a schema the details make all the sense to all
the readers. I tend to disagree using constraint names
to convey such meaning.
Whew! where was I ...
- Sundeep
--- Glenn Travis <[EMAIL PROTECTED]> wrote:
> I just found this script on another site and thought
> this group would
> benefit from it, as we are always looking for
> usefult scripts... apologies
> if the formatting is bad.
>
> Here is a script that renames foreign keys from
> system-assigned constraint
> names to more intelligible names. The names use the
> current table name and
> the referenced table name. This greatly improves
> readability, especially for
> error messages.
> DECLARE
> c_owner CONSTANT VARCHAR2 (30) :=
> 'FLEETPRO';
>
> CURSOR cons_cur
> IS
> SELECT C.owner, C.constraint_name,
> C.table_name,
> TRANSLATE
> (C.table_name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ_',
> 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
> as RTrimTable
>
> , CR.owner as ROwner, CR.constraint_name as
> RCName,
> CR.table_name as RTable,
> TRANSLATE
> (CR.table_name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ_',
> 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as
> RtrimRTable
>
> FROM all_constraints C, all_constraints CR
> WHERE C.owner = 'FLEETPRO'
> AND C.owner = CR.owner
> AND C.constraint_type in ('R')
> AND UPPER(SUBSTR(C.constraint_name,1,3)) <>
> 'FK_'
> AND C.r_constraint_name = CR.constraint_name
> ORDER BY C.table_name,CR.table_name;
> -- AND rownum < 50;
>
> cons_rec cons_cur%ROWTYPE;
>
> CURSOR col_curs
> IS
> SELECT column_name, position
> FROM all_cons_columns cc
> WHERE cc.owner = cons_rec.owner
> AND cc.constraint_name =
> cons_rec.constraint_name
> AND cc.table_name = cons_rec.table_name
> ORDER BY position;
>
> CURSOR col_R_curs
> IS
> SELECT column_name, position
> FROM all_cons_columns cc
> WHERE cc.owner = cons_rec.ROwner
> AND cc.constraint_name = cons_rec.RCname
> AND cc.table_name = cons_rec.RTable
> ORDER BY position;
>
> v_table_name
> all_constraints.table_name%type;
> v_Rtable_name
> all_constraints.table_name%type;
> v_ctr int;
>
> v_fklist VARCHAR2 (1000);
> v_fklist_R VARCHAR2 (1000);
> v_global_name VARCHAR2 (80);
> BEGIN
> OPEN cons_cur;
> v_table_name := NULL;
> v_Rtable_name := NULL;
> v_ctr := 0;
>
> LOOP
> FETCH cons_cur INTO cons_rec;
> EXIT WHEN cons_cur%NOTFOUND;
>
> v_fklist := NULL;
> v_fklist_R := NULL;
>
> FOR col_rec IN col_curs
> LOOP
> IF v_fklist IS NULL THEN
> v_fklist := '( ' || col_rec.column_name;
> ELSE
> v_fklist := v_fklist || ', ' ||
> col_rec.column_name;
> END IF;
> END LOOP;
>
> FOR col_rec IN col_R_curs
> LOOP
> IF v_fklist_R IS NULL THEN
> v_fklist_R := '( ' ||
> col_rec.column_name;
> ELSE
> v_fklist_R := v_fklist_R || ', ' ||
> col_rec.column_name;
> END IF;
> END LOOP;
>
>
> IF ((v_table_name = cons_rec.table_name)
> AND (v_Rtable_name = cons_rec.Rtable))
> THEN v_ctr := v_ctr + 1;
> Else
> v_ctr := 0;
> v_table_name :=
> cons_rec.table_name;
> v_Rtable_name := cons_rec.Rtable;
> End If;
>
> v_fklist := v_fklist || ')';
> v_fklist_R := v_fklist_R || ')';
>
> DBMS_OUTPUT.put_line
>
> 'alter table ' || cons_rec.owner || '.' ||
> cons_rec.table_name
> );
> DBMS_OUTPUT.put_line
>
> 'drop constraint ' ||
> cons_rec.constraint_name || ';'
> );
> DBMS_OUTPUT.put_line
>
> 'alter table ' || cons_rec.owner || '.' ||
> cons_rec.table_name
> );
> IF v_ctr > 0 THEN
> v_global_name := 'FK_' ||
> cons_rec.table_name || '_'
> || cons_rec.RTable ||
> to_char(v_ctr);
> ELSE
> v_global_name := 'FK_' ||
> cons_rec.table_name || '_'
> || cons_rec.RTable;
> END IF;
> IF length( v_global_name ) > 29 Then
> IF v_ctr > 0 then
> v_global_name := 'FK_' ||
> cons_rec.RtrimTable || '_'
> || cons_rec.RtrimRTable ||
> to_char(v_ctr);
> ELSE
> v_global_name := 'FK_' ||
> cons_rec.RtrimTable || '_'
> || cons_rec.RtrimRTable;
> END IF;
> END IF;
>
> DBMS_OUTPUT.put_line
>
> 'add constraint ' || v_global_name || '
> foreign key ' ||
> v_fklist
> );
> DBMS_OUTPUT.put_line
>
> 'references ' || cons_rec.ROwner || '.' ||
> cons_rec.Rtable || ' '
> ||
> v_fklist_R ||
> ';'
> );
>
>
>
> DBMS_OUTPUT.new_line ();
> END LOOP;
> END;
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Glenn Travis
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
>
=== message truncated ===
=====
Sundeep Maini
Consultant
Currently on Assignement at Marshfield Clinic WI
[EMAIL PROTECTED]
__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: sundeep maini
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
