IDS Forum

Re: quickest way to drop all referential contr....

Posted By: FRANK
Date: Friday, 3 February 2012, at 1:45 p.m.

In Response To: Re: quickest way to drop all referential contr.... (Bill Hamilton)

Bill, you want to have a

*weapon of mass destruction* ? ?

oh, NO... :-) kidding...

Thanks,
Frank

On Fri, Feb 3, 2012 at 12:15 PM, Bill Hamilton <garage_dba@hotmail.com>wrote:

> Thanks, Jack.
> I can do that with the sql below and then drop them in a FOREACH as you
> suggested.
> I was hoping there was some powerful utility to drop all referential
> constraints.
>
> SELECT x.constrname[1,64] as CON,
> i.idxname[1,64] as IDX,
> t.tabname[1,32] as TAB,
> c.colname[1,32] as COL,
> ttt.tabname[1,32] AS REFTAB
> FROM systables t, sysconstraints x, syscolumns c,
>
> sysindexes i, sysreferences r, systables ttt
> WHERE t.tabid = x.tabid and t.tabid = c.tabid
> and x.idxname = i.idxname
> and (part1 = colno or part2 = colno or part3 = colno or part4=colno or
> part5=colno or part6=colno)
> and x.constrtype = 'R'
> and r.ptabid = ttt.tabid and r.constrid = x.constrid
> ORDER BY 1;
>
> -----Original Message-----
> From: Jack Parker
> Sent: Thursday, February 02, 2012 5:37 PM
> To: ids@iiug.org
> Subject: Re: quickest way to drop all referential contr.... [26156]
>
> in essence. Muck with it as you like.
>
> Essentially join systables with syscontraints and sysindexes.
>
> FOREACH
>
> SELECT tabname, constrname, e.colname col1, f.colname col2
>
> INTO p_tabname, p_constrname, p_col1, p_col2
>
> FROM systables a, sysconstraints b, mytabs c, sysindexes d,=20
>
> syscolumns e, outer syscolumns f
>
> WHERE a.tabname=3Dlower(c.tabname)
>
> AND a.tabid=3Db.tabid
>
> AND b.constrtype=3D'P'
>
> AND b.idxname=3Dd.idxname
>
> AND a.tabid=3De.tabid
>
> AND d.part1=3De.colno
>
> AND a.tabid=3Df.tabid
>
> AND d.part2=3Df.colno
>
> drop constraint p_constrname
>
> END FOREACH
>
> On Feb 2, 2012, at 5:48 PM, Bill Hamilton wrote:
>
> > Is there a super quick way to drop ALL referential constraints and =
> indexes=20
> > behind them?=20
> >=20
> > I would like to put all indexes into a space with a larger page size.=20=
>
> > I wrote a program one time that consumes dbschema outut and writes
> sql=20=
>
> > statements to drop and then add indexes.=20
> > What I ran into was that when an index is dropped (one that I added =
> before the=20
> > constraints) the engine renames the index to=20
> > a name of his choice and then the =93add index=94 fails because there =
> is=20
> > already and index on that column.=20
> >=20
> > The simplest approach appears to be to write a program to grab all the =
> index=20
> > adds and referential constraint adds and generate sql=20
> > statements to *drop* all indexes and all constraints. Then, after =
> running that=20
> > sql just grab the part of the dbschema output that adds them=20
> > and globally replace =93btree=94 with =93btree in idxspace=94 , save =
> the file=20
> > and run it through dbaccess.=20
> > I suppose one of you gurus would hammer the syscontraints table, etc. =
> I=92m=20
> > too chicken.=20
> > That part of the schema is about 4260 lines, so doing in an editor is =
> not=20
> > looking like a plan.=20
> >=20
> > So is there a better plan? Some magic Kagle unix script that I can =
> convert to=20
> > run through Windows?=20
> >=20
> >=20
> > =
> **************************************************************************=
> *****=20
> > Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>
> >=20
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--f46d043be25673fe7e04b813b869

Messages In This Thread

• quickest way to drop all referential contraints
  Bill Hamilton -- Thursday, 2 February 2012, at 5:48 p.m.
  • Re: quickest way to drop all referential contraint
    Jack Parker -- Thursday, 2 February 2012, at 6:32 p.m.
  • Re: quickest way to drop all referential contraint
    Jack Parker -- Thursday, 2 February 2012, at 6:37 p.m.
    • Re: quickest way to drop all referential contr....
      Bill Hamilton -- Friday, 3 February 2012, at 12:15 p.m.
      • Re: quickest way to drop all referential contr....
        FRANK -- Friday, 3 February 2012, at 1:45 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.