|
|
| Re: create sql to add indexes to all foreign keys [message #570 is a reply to message #568] |
Thu, 21 February 2002 08:27  |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
