John, you are my father!
I've taken your script and tweaked it a bit more:
------------------------ SNIP ---------------------
#!/bin/sh
DBLIST="mydb1 mydb2 mytest mytestdb"
USER="uzer"
PASSWORD="passwerd"
clear
for DB in $DBLIST
do
echo "Removing all indexes (not primary) in Database: '$DB'"
TABLES=$(mysql -u$USER -p$PASSWORD --force -e "SHOW TABLES FROM $DB"
\
| awk '{if(NR>1) print $1;}')
for t in $TABLES;
do
echo -e "\tFixing: '$t'"
echo "SHOW INDEX FROM $t" \
| mysql -u$USER -p$PASSWORD --force $DB \
| awk '{if($3 !~ /Key_name/ && $3 !~ /PRIMARY/) print $1"
"$3}' \
| awk '{print "ALTER TABLE "$1" DROP INDEX "$2";"}' \
| mysql -u$USER -p$PASSWORD --force $DB
done
done
> -----Original Message-----
> From: John Thorpe [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, March 30, 2004 1:49 PM
> To: Daevid Vincent
> Cc: [EMAIL PROTECTED]
> Subject: Re: Drop all keys / indexes on a table?
>
> I've been using comand line piping through awk to handle
> mass tables modifications and listings. E.g. in your case
> something like the following would hit every index, except
> the primary keys, in table tablename in the test database.
>
> echo "show index from tablename" |
> | mysql -uuser -ppswd test
> | awk '{if($3 !~ /Key_name/ && $3 !~ /PRIMARY/) print $1" "$3}'
> | awk '{print "alter table "$1" drop index "$2";"}'
> | mysql -uuser -ppswd test
>
> To hit every table in the test database:
>
> mysql -uuser -ppswd -e "show tables from test"
> | awk '{if(NR>1) print "show index from "$1";"}'
> | mysql -uuser -ppswd test
> | awk '{if($3 !~ /Key_name/ && $3 !~ /PRIMARY/) print $1" "$3}'
> | awk '{print "alter table "$1" drop index "$2";"}'
> | mysql -uuser -ppswd test
>
> John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
