Get data of all tables in database

Question

I'm using mysql version 5.7, it has around 2000 tables, each of tables has columns gmt_modified as mandatory.
So now i want to build a report to see how much data in each time range, eg: 3-6-9-12 months.
what i'm about to do is base on gmt_modified (timestamp) to do that.
I just wonder mysql support the way to build that report or not
or anybody know how can we select gmt_modified of all tables in a schema?
We can list columns in all tables by this way:

select * 
  from information_schema.columns 
 where table_schema = 'your_DB_name' 
   and table_name = 'Your_tablename'

but how to select data of column as well?
Thanks

'a report to see how much data in each time range, eg: 3-6-9-12 months.' - per table or over all tables (or both) and what does how much mean is that a count or number of bytes or value? Are you aware of dynamic sql? Why does your query select all column when you seem only to be interested in table name and why the filter on table name when all tables are of interest? — P.Salmon, May 26 '22 at 05:53

Caius Jard · Answer 1 · 2022-05-26T06:03:00.863

You can write an SQL that writes an SQL, and then copy the SQL into the query window and execute it

  select CONCAT('
    SELECT ''', table_name, ''', (YEAR(CURDATE())*12 + MONTH(CURDATE()) - (YEAR(gmt_modified)*12 + MONTH(gmt_modified)))/3 as period, COUNT(*) as ctr 
    FROM ', table_name, ' 
    WHERE gmt_modified > DATE_ADD(curdate(), INTERVAL Year -1) 
    GROUP BY period UNION ALL'
  )

  from information_schema.columns 
  where column_name = 'gmt_modified'

This will essentially write 2000 queries (or however many tables have a gmt_modified column) as a query result that you can then copy into another query window, remove the trailing UNION ALL that is causing a syntax error, and then run it. It might take a long time..

Note, I've picked on a relatively naive way of calculating a month diff: months are difficult because they vary in length. Perhaps a 30/60/90 days etc would suit better, do a period diff on hours and divide by 24 then 30

nazim · Answer 2 · 2022-05-26T08:07:02.320

To approach this problem, you first need to generate main text of your query from a query-building-query... something like this:

select concat('  select gmt_modified FROM ',table_name,' UNION ALL')
from information_schema.tables where table_schema = 'your_DB_name';

This will give you output something like:

select gmt_modified FROM table1 UNION ALL
select gmt_modified FROM table2 UNION ALL
select gmt_modified FROM table3 UNION ALL
select gmt_modified FROM table4 UNION ALL
select gmt_modified FROM table5 UNION ALL
select gmt_modified FROM table6 UNION ALL
select gmt_modified FROM table7 UNION ALL

Remove the last words UNION ALL from last line. Then you wrap it with the aggregation query. like this

select count(gmt_modified) as record_count FROM (
    select gmt_modified FROM table1 UNION ALL
    select gmt_modified FROM table2 UNION ALL
    select gmt_modified FROM table3 UNION ALL
    select gmt_modified FROM table4 UNION ALL
    select gmt_modified FROM table5 UNION ALL
    select gmt_modified FROM table6 UNION ALL
    select gmt_modified FROM table7
) details
GROUP BY QUARTER(gmt_modified);

So basically, you'll need to regenerate the main text, each time you remove/add a table to the db.

You probably want to use `UNION ALL`, and not `UNION`. see: [What is the difference between UNION and UNION ALL?](https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all) — Luuk, May 26 '22 at 08:03
And using `GROUP_CONCAT` would be nicer, like: `select group_concat(' select gmt_modified FROM ',table_name separator ' UNION ALL') from information_schema.tables where table_schema = 'your_DB_name';` — Luuk, May 26 '22 at 08:13

Get data of all tables in database

2 Answers2