Add a new column in all mysql tables

I have a practice table like this:

    id     | otherColumn1 | otherColumn2 | otherColumn3
--------------------------------------------------------
    1      |  othervalue1 |  othervalue2 | othervalue3 
    2      |  othervalue1 |  othervalue2 | othervalue3 

I have many other tables:

Person
Address
Contact
------
------
------

Now my task is to add practiceId column in every other table which will be a foreign key from pr开发者_高级运维actice table. Remember that some tables already have practiceId column so I don't need to change those tables.

Problem is that there are many tables. Can I do this with less queries Or can I write a efficient PHP script for this ?

I already have a php script that can get tables names from a particular database.

Thanks

No, there really isn't a way to add a field to multiple tables at once. You will need to write a script for it. It shouldn't be a hard script to write, though:

A simple SHOW TABLES query will get you a list of tables which you can then loop through in PHP.

Within the loop, a SHOW COLUMNS FROM tablename query will give you the fields in the current table, which you can use to check if it already has the practiceId field, and then add it if necessary.

To add the field, use ALTER TABLE tablename ADD COLUMN practiceId INT (I've assumed INT, but replace with however your field needs to be defined if you're using a different data type)

To add an index on the new column, you can also use ALTER TABLE, or use CREATE INDEX. To create a foreign key contraint use ALTER TABLE with ADD FOREIGN KEY argument (But I believe this only works with InnoDB tables).

Reference:

• SHOW TABLES
• SHOW COLUMNS
• ALTER TABLE
• CREATE INDEX
• Foreign key contraints