How to get list of tables, that don`t have a specific column in MySQL

views:

answers:

How to get list of tables, that don`t have a specific column in MySQL

I need to have a list of tables in MySQL, that don`t have a column 'created' or 'modified', so I can add them if non existant. How can I achieve this?

Thanks in advance for any help / hint.

+1 A:

Query INFORMATION_SCHEMA database for this.

http://dev.mysql.com/doc/refman/5.1/en/information-schema.html

FractalizeR 2010-10-05 09:35:24

Thanks. The following query did the trick: SELECT c.table_name, COLUMN_NAMEFROM information_schema.`TABLES` t INNER JOIN information_schema.`COLUMNS` c ON t.table_name = c.table_namewhere column_name = 'created'OR column_name = 'modified' //for those interested. Thanks

tombom 2010-10-05 09:49:00

@tombom Welcome :)

FractalizeR 2010-10-05 09:49:49

You can do this from PHP. Connect to the database and browse the mysql table. It will have all table definitions, then run DESCRIBE on each table, that will get you the structure.

I can give you some code examples if you like

Yarek T 2010-10-05 09:36:19

Thanks, but PHP is not an option.

tombom 2010-10-05 09:42:07

// Nevermind, glad you found a solution

Yarek T 2010-10-05 11:06:02

ansaurus

tags:

views:

answers:

How to get list of tables, that don`t have a specific column in MySQL

related questions