On Wed, 30 Dec 1998, Samir Mathur wrote:

> Hi Ari,
>
> I need to generate an Alter table script to add foreign keys for all the
> tables in a schema. Kinda struggling with it!! Have made a script for
> the primary keys though -- kindly help!!!
>
> set heading off
> set linesize 100
> set pagesize 1000
> set termout off  
> set echo off
> set feedback off
>
>  spool d:\tsql\gen_prim1.sql
>  select decode(POSITION,1,'ALTER TABLE '||a.TABLE_NAME||
>       ' ADD (constraint '||a.CONSTRAINT_NAME||' PRIMARY KEY ( ')||
>             decode(position,d.pos,column_name||'));',column_name||',')
>  from user_cons_columns a , user_constraints b,
>  (SELECT MAX(POSITION) pos ,table_name tab FROM user_cons_columns X
> WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS Y
> WHERE Y.CONSTRAINT_TYPE='P'
> AND Y.TABLE_NAME=X.TABLE_NAME)
> group by table_name) d
>  where a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
>  and a.TABLE_NAME = b.TABLE_NAME
>  and a.TABLE_NAME = d.tab
>  and a.OWNER   = b.OWNER          
>  and b.CONSTRAINT_TYPE = 'P'
>  order by a.table_name, A.CONSTRAINT_NAME, a.position;
>
>
> spool off
> set termout on
> set echo on
> set feedback on
>
> Would appreciate a prompt reply!!!
> Thanks...
> Warm regards,
>
Samir,

Glad that you like my page. I have not had time to look at your SQL that
much. I do have a quick suggestion. If you export your schema (no need to    
export the data), and then import it using the INDEXFILE option, you will
get all of your CREATE INDEX syntax. I use this a lot and hope that you
will too.

Export the data:

exp userid=username/password file=expdat.dmp rows=n owner=OWNER_NAME

This will create the expdat.dmp file with no data. To generate the CREATE
INDEX statements, do:

imp userid=username/password file=expdat.dmp full=y indexfile=FILE_NAME

The FILENAME will now have all CREATE INDEX statements that you need!

Best regards,    
-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 245+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page eight="18"> Back to Ari Kaplan's Home Page