adding column to all tables in a mysql database (unknown table names)

Question

I need to add a primary key to a set of tables in a given database. I do not know how many tables there will be or what their specific names are. They will all be of the for datatable_##, where ## will vary based everywhere my script will be run.

To add the primary key, I am using this query:

alter table datatable_??
add column ID int auto_increment not null first,
add primary key (ID);

So, I need this to run on every table in the database. It seems like I can do this in a PHP script or something, but is their a simpler way to do this just in sql script?

Answer 1

select concat('alter table ',table_name,' add column ID int auto_increment not null primary key first;')
from information_schema.tables
where table_schema = 'db_name' and table_type = 'base table';

Once you have the output, copy and paste and run them all.

Answer 2

If you want to fully script this, you can do something like this:

SELECT CONCAT('ALTER TABLE ',
table_schema,
'.',
table_name,
' ADD COLUMN id INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;') AS ddl
INTO OUTFILE '/tmp/alter_table.sql'
FROM information_schema.tables
WHERE table_schema = 'db_name' 
AND table_type = 'base table';

\. /tmp/alter_table.sql