SQL MariaDB - Loop this query for every table in every database with exceptions

Question

I have large databases that I prune on a monthly basis using the query below. However, I have hundreds of tables and would like to make this into a foreach loop for each table in the databases.

I know how to achieve this loop in PHP but would be great to have it in pure SQL to run directly as a query on the database.

The tables prefixed with noprune such as noprune_master_tableZ need to be left out of the loop.

Where do I begin? Thanks

START TRANSACTION;
set @N := (now());
CREATE TABLE master_tableA_snapshot AS SELECT * FROM master_tableA where insertDATE < date_sub(@N,INTERVAL 32 DAY);
SELECT * from master_tableA_snapshot INTO OUTFILE '/tmp/master_tableA_snapshot_TODAYSDATE.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
DELETE from master_tableA where insertDATE < date_sub(@N,INTERVAL 32 DAY);
DROP TABLE master_tableA_snapshot;
COMMIT;

Answer 1

You've really only got two statements with a helper variable simplified as:

set @N := date_sub(NOW(),INTERVAL 32 DAY);

SELECT *
FROM master_tableA
WHERE insertDATE < @N
INTO OUTFILE '/tmp/master_tableA_snapshot_TODAYSDATE.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

DELETE from master_tableA where insertDATE < @N;

PHP select table names with a query:

SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE SCHEMA='mydatabase'
  AND TABLE_NAME NOT LIKE 'noprune%';