How do I change all tables in my database to use AUTO_INCREMENT=1

I have about 30 tables in my database:

table1 table2 table3 table4 table5 etc.

I want all tables to use AUTO_INCREMENT=1, how do I modify the tables?

Here is the sample DDL of开发者_开发问答 one of the tables. I have never defined AUTO_INCREMENT in any of the tables, it is fetching the value by default.

CREATE TABLE `amenities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

To change the value of the AUTO_INCREMENT counter to be used for new rows, do this:

ALTER TABLE `table_name` AUTO_INCREMENT = 1;

To update all your 31 tables you can use this php script:

<?php
$tables = array('table1','table2','tableX'); //continue here
foreach($tables as $update)
{
     mysql_query("ALTER TABLE `".$update."` AUTO_INCREMENT = 1;");
}
?>

select concat('alter table ',table_name,' auto_increment = 1;') 
from information_schema.tables
where table_schema = 'your_db';

then run the output generated. By the way this is a strange questions. If you use truncate table_name your auto_increment value will restart from 1.

edit. You could use into outfile to redirect the queries within a txt file and then recall it.

select concat('alter ',table_name,' auto_increment = 1;') 
into outfile 'd:/queries.txt'
lines terminated by '\r\n'
from information_schema.tables
where table_schema = 'your_db'