# Drop tables where the last update is over 1 week ago

I can see in PMA that it stores information for every table.

I was wondering if there was an SQL query to drop any table in the database that have a last update time of more than 1 week ago?

-

## migrated from stackoverflow.comMay 23 '13 at 12:26

This question came from our site for professional and enthusiast programmers.

What do you mean last update time. 1.When the table structure was changed or 2.When was the last entry done. –  Luv May 23 '13 at 11:46

All the metadata about tables are stored in the db named information_schema.

Try this:

SELECT
TABLE_NAME
FROM
information_schema.TABLES
WHERE
UPDATE_TIME < (NOW()-INTERVAL 1 WEEK)


UPDATE: i forgot to mention this will get you all the tables that you need to drop, after that you can build the DROP statements.

-

Use the below Query:

select CONCAT('drop table ',TABLE_NAME)
from INFORMATION_SCHEMA.TABLES
where update_time < (now()-interval 1 week)


Reference

Additionally, you may add the above query to a prepared statements in order to execute the drop(s) as is shown in the answer from here

-
This doesn't drop the table, but instead you just create a result containing drop table in all the rows. –  maythesource.com May 23 '13 at 11:53
@meewoK Do we have to write the SP too? I don't like to spoon feed, hence i have given Pseudo code. –  Luv May 23 '13 at 11:56
ok good point. Editing to just add a reference to Prepared Statement. Argh...when the edit goes through I can reverse that -1... –  maythesource.com May 23 '13 at 11:58
@meewoK I have edited the post. Thanks for your information, –  Luv May 23 '13 at 12:03
Your query is wrong. You should use CONCAT(). In MySQL + doesn't concatenate strings. –  Federico May 23 '13 at 12:07

No, but you can use a Stored Procedure which queries the information_schema and composes the DROP TABLEs dynamically.

CREATE PROCEDURE test.drop_old_tables()
MODIFIES SQL DATA
COMMENT 'Drops tables older than 1 week'
BEGIN
DECLARE eof BOOLEAN DEFAULT FALSE;
DECLARE db_name VARCHAR(64);
DECLARE tab_name VARCHAR(64);

DECLARE crs_old_tabs CURSOR FOR
SELECT
TABLE_SCHEMA, TABLE_NAME
FROM
information_schema.TABLES
WHERE
UPDATE_TIME < (NOW() - INTERVAL 1 WEEK);

DECLARE CONTINUE HANDLER
BEGIN
SET eof = TRUE;
END;

lbl_old_tabs:
LOOP
FETCH NEXT FROM crs_old_tabs INTO db_name, tab_name;

IF eof IS TRUE THEN
LEAVE lbl_old_tabs;
END IF;

SET @sql_drop = CONCAT('DROP TABLE ', REPLACE(db_name, '', ''), '.', REPLACE(tab_name, '', ''), ';');
PREPARE stmt_drop;
EXECUTE stmt_drop;
DEALLOCATE PREPARE stmt_drop;
END LOOP;

SET @sql_drop = NULL;
END;


I didn't try this procedure, so it's possible that you'll have to fix one error or two.

-