Home » SQL & PL/SQL » SQL & PL/SQL » create sql to add indexes to all foreign keys
create sql to add indexes to all foreign keys [message #568] Thu, 21 February 2002 07:18 Go to next message
Katerine
Messages: 1
Registered: February 2002
Junior Member
Does anyone have a script which creates the sql required to add indexes to all foreign keys in a database?
Re: create sql to add indexes to all foreign keys [message #570 is a reply to message #568] Thu, 21 February 2002 08:27 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
declare
Cursor c1 is select distinct table_name,constraint_name from user_constraints
where constraint_type='R' ;
cursor c2(cons varchar2) is select a.constraint_name,column_name,a.table_name
from user_cons_columns a
where a.constraint_name=cons
ORDER BY POSITION;
crec1 c2%rowtype;
L_SQL VARCHAR2(500);
ctr number:=0;
BEGIN
DBMS_OUTPUT.ENABLE(100000);
FOR CREC IN C1 LOOP
l_sql:=null;
OPEN c2(crec.constraint_name);
LOOP
fetch c2 into crec1;
if c2%notfound then
if l_sql is not null then
execute immediate l_sql||')';
end if;
exit;
end if;
IF c2%rowcount=1 then
L_SQL:='Create index '||crec1.constraint_name||'_idx on '||crec.table_name||'('||crec1.column_name;
ELSE
l_sql := l_sql||','||crec1.column_name;
end if;
END loop;
END LOOP;
END;
Previous Topic: getting error ora-01403 data not found when i execute a simple select statement
Next Topic: not equal- != or <>
Goto Forum:
  


Current Time: Mon Sep 27 22:29:04 CDT 2021