CODEWITHSUNDEEP
mysql
Hoja
Hoja

Reputation: 1207

Execute a statement for every table in a database

I know this query for changing the charset and collation of mysql table. 

alter table <some_table> convert to character set utf8 collate utf8_unicode_ci;

But I need a query for changing all the tables in a db. Is there any possible solutons.

Upvotes: 0

Views: 128

Answers (1)

fancyPants
fancyPants

Reputation: 51888

I have written this procedure to execute statements for every table in a database:

DROP PROCEDURE IF EXISTS sp_forEveryTable;
DELIMITER $$
CREATE PROCEDURE sp_forEveryTable(IN p_schema varchar(50), IN p_stmt varchar(100))
BEGIN

  DECLARE done INT DEFAULT FALSE;
  DECLARE v_tbl varchar(50);
  DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = p_schema;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO v_tbl;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @sql := REPLACE(p_stmt, '?', v_tbl);

    IF (UPPER(p_stmt) LIKE 'SELECT %') THEN
        SET @sql := CONCAT('SELECT "', v_tbl, '", ', SUBSTRING(@sql FROM 7));
    ELSE 
        SELECT v_tbl AS 'Execute statement for following table:';
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;
END $$
DELIMITER ;

Use it like this:

CALL sp_forEveryTable('your_database_name', 'ALTER TABLE ? CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci');

To have the tables created in the future in this database have the utf8 character set and collation as default use the statement given in this answer.

Upvotes: 2

Related Questions