Skip navigation links

MySQL Forums :: InnoDB :: alter multiple tables


Advanced Search

alter multiple tables
Posted by: Ozkan Beserek ()
Date: February 12, 2013 06:02AM

I need to alter several tables in one database. The following commands needs to be executed on each table:

alter table tbl modify column ID bigint(20) NOT NULL;
alter table tbl DROP PRIMARY KEY;
alter table tbl add column rowid int(11) NOT NULL AUTO_INCREMENT primary key;
alter table tbl ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

And to know which tables that needs to be altered, I do the following:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('ID')
AND TABLE_SCHEMA='cpas2000ozkan';

This gives me a list of all the table names.

I can use these table names in a procedure. But I am new to this. Anyone can give me a hand with creating a procedure for doing this?

Options: ReplyQuote


Subject Views Written By Posted
alter multiple tables 1756 Ozkan Beserek 02/12/2013 06:02AM
Re: alter multiple tables 785 Rick James 02/13/2013 07:40PM
Re: alter multiple tables 613 Ozkan BE 02/14/2013 02:23AM
Re: alter multiple tables 653 Rick James 02/14/2013 10:02PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.