CODEWITHSUNDEEP
mysqlsql
Creshal
Creshal

Reputation: 483

(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_date>=42 obviously doesn't work, but what would be the best way of doing it?

Upvotes: 0

Views: 480

Answers (3)

MikeyKennethR
MikeyKennethR

Reputation: 608

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 ...

Upvotes: 2

Paul Williams
Paul Williams

Reputation: 17040

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.

Upvotes: 0

Bas Slagter
Bas Slagter

Reputation: 9929

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.

Upvotes: -1

Related Questions