need help with constraints & indexes
I have over 1000 databases that have auto generated constraint names and I need a clean way to drop them and recreate with given names. I tried delete from sysconstraints where constrtype !-"N"; (I wanna keep the not nulls) to no avail...it leaves the auto genned indexes. so I tried delete from sysindexes where tabid > 99 that corrupted the tables; any help is greatly appreciated. I dont wanna have to try and figure out all the constraint names and drop them manually. TIA! Mike South The BEST in adult Video www.mikesouth.com
My God Man, cutting holes in this man's head isn't the answer!!!
Select the indexes you want to drop from sysindexes and use vi to hack
commands around them.
Following is an example of how to do this. I'm doing this off the top
of my head, so make sure you check it thouroughly before running
anything. I make no guarantees. Also, make sure you have a backup.
With 1000 (tables, I hope you mean), running this will take quite a
while, I would think.
unload to mysql.sql select idxname from sysindexes where ...
on the command line:
dbschema -d <database> schema.sql
for i in `cat mysql.sql`
do
echo "grep $i schema.sql" >> rebuild.sql
done
Check this file to make sure it's right and make the changes to the
index names.
vi mysql.sql
:g/^/s//drop index /g
:g/$/s//;/g
Running this SQL will drop the indexes.
Running rebuild.sql will recreate the indexes.
>
> I have over 1000 databases that have auto generated constraint names
> and I need a clean way to drop them and recreate with given names. I
> tried delete from sysconstraints where constrtype !-"N"; (I wanna
> keep the not nulls) to no avail...it leaves the auto genned indexes.
> so I tried delete from sysindexes where tabid > 99 that corrupted the
> tables;
>
> any help is greatly appreciated. I dont wanna have to try and figure
> out all the constraint names and drop them manually.
>
> TIA!
>
> Mike South
> The BEST in adult Video
> www.mikesouth.com
>
--
# unrm /
ksh: unrm: not found
# man cpio
Sent via Deja.com http://www.deja.com/
Before you buy.
Get my dbschema replacement utility, myschema, and my set of schema scripts.
Myschema automatically generates NORMAL indexnames for all auto generated
indexes created by constraints so you can easily reorg them. The scripts
provide good templates for making awk scripts to post-process schema files
to do things like drop and recreate constraints and indexes, even if the
one you are looking for is not already there (many are). Myschema is
contained in the package utils2_ak and the scripts are in utils4_ak both
available from the IIUG Software Repository. As to how to quickly drop
all constraints, the SQL below will generate the SQL script you want, BUT
remember to get the myschema output to replace the constraints first:
Mike South wrote:
>
> I have over 1000 databases that have auto generated constraint names
> and I need a clean way to drop them and recreate with given names. I
> tried delete from sysconstraints where constrtype !-"N"; (I wanna
> keep the not nulls) to no avail...it leaves the auto genned indexes.
> so I tried delete from sysindexes where tabid > 99 that corrupted the
> tables;
>
> any help is greatly appreciated. I dont wanna have to try and figure
> out all the constraint names and drop them manually.
OUTPUT TO kill_constraints.sql
SELECT "ALTER TABLE "||trim(trailing from tabname)
|| " DROP CONSTRAINT " || trim(trailing from constrname) || ";"
FROM systables st, sysconstraints sc
WHERE st.tabid = sc.tabid
AND constrtype !- "N";
Art S. Kagel