0

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?

Creshal
  • 493
  • 1
  • 4
  • 15
  • 1
    I think you must explicitly include tables you want to query... – Marco Sep 09 '11 at 13:46
  • 1
    What exactly is it you are trying to achieve? Why do you need all rows (matching the criteria) from all tables containing that column? That sounds like a very strange thing to do –  Sep 09 '11 at 14:00
  • Possible Duplicate: http://stackoverflow.com/questions/639531/mysql-search-in-all-fields-from-every-table-from-a-database – reggie Sep 09 '11 at 14:00
  • @a_horse_with_no_name I need to delete all columns past a certain creation date (long, messy story), no matter in which table they are in. Yes, I'm aware that this is potential suicide and shouldn't be used normally. I still need to do it. – Creshal Sep 12 '11 at 13:00

3 Answers3

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

MikeyKennethR
  • 600
  • 4
  • 16
  • So, I need some external logic to assemble the statements and can't do it in pure SQL? That's what I was trying to avoid. – Creshal Sep 12 '11 at 13:02
  • You can do a pure sql statement. The systemtables that are invovled, are just like any other table. Shall I do a ex. for you ? – MikeyKennethR Sep 13 '11 at 07:48
0

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.

Paul Williams
  • 16,585
  • 5
  • 47
  • 82
-1

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.

Bas Slagter
  • 9,831
  • 7
  • 47
  • 78
  • 4
    That statement is **very** dangerous as it creates a cartesian product between those two tables because no join condition is supplied. Even with moderately sized tables this will generate a huge result –  Sep 09 '11 at 13:55
  • You are absolutely right...but is answers the question. Good comment though! – Bas Slagter Sep 09 '11 at 13:56
  • -1 , you need a union, not a cross join. `select f1 from table1 union select f2 from table2 union select ....` – Johan Sep 09 '11 at 16:51
  • The point was to avoid having to type 200 table names manually, so this is quite useless… – Creshal Sep 12 '11 at 12:57