MySQL clone of sp_msforeachtable

Many SQL Server DBAs and Developers get a  lot of use out of the undocumented sp_MsForEachTable system stored procedure. Here’s an attempt at creating a functional version for MySQL. The procedure makes use of prepared statements, which do have some limitations, so some uses may not translate across. This is far from production ready so use with lots of caution.

Create the below stored procedure in the database you wish to use it in.

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `usp_mysql_foreachtable`$$
 
CREATE PROCEDURE `usp_mysql_foreachtable`(IN sql_string VARCHAR(1000))
    LANGUAGE SQL
    NOT DETERMINISTIC
    COMMENT 'Functional clone of sp_MsForEachTable'
    BEGIN
 
	DECLARE var_tablename VARCHAR(100);
	DECLARE last_row BIT;
 
	DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME
					FROM information_schema.TABLES
					WHERE TABLE_TYPE = 'BASE TABLE'
					AND TABLE_SCHEMA = DATABASE();
 
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET last_row = 1;
 
	OPEN table_cursor;
	FETCH table_cursor INTO var_tablename;
 
	SET last_row = 0;
	SET @var = '';
 
	lbl_table_cursor: LOOP
 
		SET @qry = REPLACE(sql_string, '?', var_tablename);
 
		PREPARE q FROM @qry;
		EXECUTE q;
		DEALLOCATE PREPARE q;
 
		FETCH table_cursor INTO var_tablename;
		IF last_row = 1 THEN
			LEAVE lbl_table_cursor;
		END IF;
	END LOOP lbl_table_cursor;
 
	CLOSE table_cursor;
 
    END$$
 
DELIMITER ;

Usage is quite simple, just pass in a query with a ‘?’ in place of where the table name should be. I have verified the below use cases function as expected.

Select 5 rows from each table

CALL usp_mysql_foreachtable('SELECT * FROM ? LIMIT 5;');

Count the number of rows in each table

CALL usp_mysql_foreachtable('SELECT ''?'', COUNT(*) AS Rows FROM ?');

Repair all tables in the database

CALL usp_mysql_foreachtable('REPAIR TABLE ?');

Change all tables to use the InnoDb storage engine

CALL usp_mysql_foreachtable('ALTER TABLE ? ENGINE=InnoDB');

Reset the AUTO_INCREMENT to zero in all tables

CALL usp_mysql_foreachtable('ALTER TABLE ? AUTO_INCREMENT = 0');

2 Comments

  1. Sonny says:

    Hi Rhys,

    Thanks for this. It is coming in really handy as I migrate a SQL Server application to MySQL.

    There was one small problem. It seems that the stored procedure causes an error if run against a database with no tables:-
    blah blah.. MySQL server version for the right syntax to use near ‘NULL’ at line 1.. blah blah

    Removing the first FETCH and moving the IF check to the top of the loop seems to fix it though :-

    lbl_table_cursor: LOOP

    FETCH table_cursor INTO var_tablename;
    IF last_row = 1 THEN
    LEAVE lbl_table_cursor;
    END IF;

    SET @qry = REPLACE(sql_string, ‘?’, var_tablename);

    PREPARE q FROM @qry;
    EXECUTE q;
    DEALLOCATE PREPARE q;

    END LOOP lbl_table_cursor;

    Hope this helps anybody running into the same problem.

Leave a Reply

Current day month ye@r *