(My)SQL: Wildcard in FROM clause?

I have a database with about 200 tables, and need to do a query on all tables containing a certain column (creation_date), but not all tables have that column. SELECT * FROM * WHERE creation_da开发者_如何学编程te>=42 obviously doesn't work, but what would be the best way of doing it?

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, TABLE_NAME,
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name ='creation_date'] 

copied directly from MySQL - you need to loop thru these system tables and the list will contain those ...

then you can build your SQL statements and be sure that they work ...

You could build a dynamic SQL query from metadata. I would approach this like:

1. Get a list of tables
2. For each table,
3. See if the table has the column creation_date
4. If it does, add your query on this table to the dynamic query
5. Union the results together

You might also be able to create a view on the multiple tables. Then you can just query the view.

You can try to include the table you want to query like so:

SELECT * FROM table1 a, table2 b WHERE a.creation_date>=42

You cannot use a wildcard in the from. In the way as shown above you can specify on which tables the where clause must apply. You can than leave out the ones that don't have the column.

So in the example query, table1 (alias a) has the column, and table2 (alias b) does not.