Have you or anyone else on the list used this script?
Successfully? More than once?
Jared
YTTRI Lisa <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/19/02 08:43 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: FW: rename foreign keys from system-assigned constraint names
to
Here is something that was posted before - I haven't tried it so I don't
know how well it works (if it does).
-----Original Message-----
Sent: Wednesday, August 08, 2001 4:23 PM
To: Multiple recipients of list ORACLE-L
more
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:
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).
