How to add a particular field to all table if it is not exists?

Question

I want to add a field name called UserId datatpe varchar(25) in all the tables.If the table already contains this field I dont want to add.Is it possible by using SQL query.Please give me some Ideas.

Answer 1

You can use SHOW TABLES to get a list of all the tables in the database, and SHOW CREATE TABLE or DESCRIBE to get the specification of each table. This can then be used in application code to create the field. Exactly how to do it depends on your programming language.

I don't think it's possible using only SQL, but I'm not an SQL guru so don't take my word for it :-)

Answer 2

Refer to http://www.lost-in-code.com/programming/mysql/mysql-check-if-field-exists/

It should be doable using something like:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=’table’ AND column_name=’UserId’) 
BEGIN 
 Alter Table....
END