Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I can see in PMA that it stores information for every table. enter image description here

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?

share|improve this question
1  
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
add comment

migrated from stackoverflow.com May 23 '13 at 12:26

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

3 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
    
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
1  
@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
1  
@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
show 1 more comment

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
        FOR NOT FOUND
    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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.