List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:March 30 2004 11:03pm
Subject:RE: Drop all keys / indexes on a table?
View as plain text  
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:jthorpe@stripped] 
> Sent: Tuesday, March 30, 2004 1:49 PM
> To: Daevid Vincent
> Cc: mysql@stripped
> 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

Thread
Drop all keys / indexes on a table?Daevid Vincent29 Mar
  • Re: Drop all keys / indexes on a table?Victoria Reznichenko30 Mar
    • RE: Drop all keys / indexes on a table?Daevid Vincent30 Mar
      • Re: Drop all keys / indexes on a table?PeterWR30 Mar
        • RE: Drop all keys / indexes on a table?Daevid Vincent30 Mar
          • Re: Drop all keys / indexes on a table?John Thorpe30 Mar
            • RE: Drop all keys / indexes on a table?Daevid Vincent31 Mar
      • using result as a fieldMojtaba Faridzad30 Mar
      • RE: Drop all keys / indexes on a table?Daevid Vincent30 Mar