I actually suggested that last night, and thought better of it because
the alter ignore was so much simpler...
On Dec 3, 2007 11:18 AM, Rolando Edwards <[EMAIL PROTECTED]> wrote:
> You may want to check to see if the index exists first.
> Just query the table INFORMATION_SCHEMA.STATISTICS:
>
> SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
> WHERE table_schema = '<given schema>'
> AND table_name = '<given table name>'
> AND index_name = '<given index name>';
>
> This returns the number of columns the index contains.
>
> If this query returns zero(0), then the index does not exist.
>
> If this query returns a positive number, then call
> ALTER TABLE <tbl-name> DROP INDEX <index-name>;
>
> You may want to write this stored procedure to do this.
> Here is the Code (change the 'util' schema to the schema you want) :
>
> DELIMITER $$
>
> DROP PROCEDURE IF EXISTS `util`.`sp_DropIndex` $$
> CREATE PROCEDURE `util`.`sp_DropIndex` (tblSchema VARCHAR(64),tblName
> VARCHAR(64),ndxName VARCHAR(64))
> BEGIN
>
> DECLARE IndexColumnCount INT;
> DECLARE SQLStatement VARCHAR(256);
>
> SELECT COUNT(1) INTO IndexColumnCount
> FROM information_schema.statistics
> WHERE table_schema = tblSchema
> AND table_name = tblName
> AND index_name = ndxName;
>
> IF IndexColumnCount > 0 THEN
> SET SQLStatement = CONCAT('ALTER TABLE `',tblSchema,'`.`',tblName,'`
> DROP INDEX `',ndxName,'`');
> SET @SQLStmt = SQLStatement;
> PREPARE s FROM @SQLStmt;
> EXECUTE s;
> DEALLOCATE PREPARE s;
> END IF;
>
> END $$
>
> DELIMITER ;
>
> Give it a try !!!
>
>
> -----Original Message-----
> From: Rob Wultsch [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 03, 2007 12:51 PM
> To: Adam Lipscombe
> Cc: mysql@lists.mysql.com
> Subject: Re: how to "drop index if exists"
>
> On Nov 29, 2007 4:34 AM, Adam Lipscombe <[EMAIL PROTECTED]> wrote:
> > Folks
> >
> >
> > How can one conditionally drop an index in MySQL?
> >
> > Googling shows that the "drop index" does not support an "if exists"
> > qualifier - apparently a bug
> > has been raised but as far as I know its not fixed yet.
> >
> > Does anyone know of a work-around?
> >
> > TIA - Adam
> >
>
> Sent my first response late at night and not the community... And the
> response also sucked.
> DROP INDEX is mapped to ALTER TABLE tbl_name
> DROP INDEX.
> ALTER IGNORE TABLE tbl_name
> DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your
> goal?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
Rob Wultsch
(480)223-2566
[EMAIL PROTECTED] (email/google im)
wultsch (aim)
[EMAIL PROTECTED] (msn)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
