useful-qry/table-controls/truncate-all-tables(위험).sql

DROP PROCEDURE IF EXISTS trunctables;
DELIMITER ;;
CREATE  PROCEDURE trunctables(theDb varchar(64))
SQL SECURITY INVOKER
BEGIN
    declare tname varchar(64);
    declare table_except text;
    declare is_found int;
    declare tcursor CURSOR FOR 
        SELECT table_name FROM information_schema.tables WHERE table_type <> 'VIEW' AND table_schema = theDb order by table_name;

    set table_except = 'dbr_agroup';
    SET FOREIGN_KEY_CHECKS = 0; 
    OPEN tcursor;
    l1: LOOP
        FETCH tcursor INTO tname;
        if tname = NULL then leave l1; end if;
        set is_found = instr(table_except, tname);
        insert into aaaa (aaa, bbb) values(table_except, tname);
        if is_found = 0 then
            set @sql = CONCAT('truncate `', theDB, '`.`', tname, '`');
            PREPARE stmt from @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        end if;
    END LOOP l1;
    CLOSE tcursor;
    SET FOREIGN_KEY_CHECKS = 1; 
END ;;
DELIMITER ;

call trunctables('ssokkk_db');