Is there a way in MySQL to execute a result set from information_schema

Question

This gives me a list of queries I would like to execute:

select CONCAT('ALTER TABLE ',table_name,' DROP edit_time;') AS query FROM information_schema.COLUMNS    where column_name = 'edit_time' AND table_schema = 'homestead';

Result:

|query|
ALTER TABLE node_fields DROP edit_time;
ALTER TABLE node_list_role DROP edit_time;
ALTER TABLE node_list_versions DROP edit_time;
ALTER TABLE node_lists DROP edit_time;
.. (etc) ..

This returns rows of all tables that I want to drop that field on. But is there any way to do something like:

EXECUTE {that query}

or

FOREACH {results of that query} EXECUTE( {that row} )

I don't know mysql procedures or logical statements that well so this is new to me.

See this question, it's basically what you need: https://stackoverflow.com/questions/999200/is-it-possible-to-execute-a-string-in-mysql — pilsetnieks, May 26 '17 at 18:11

A C · Answer 1 · 2017-05-26T21:04:40.813

Select all that text into a variable and use it to create a prepared statement, which you can then execute. Be aware that there are some things that are not allowed in a prepared statement, which may include your table modifications.

Edit: As mentioned by @BerndBuffen in the comments below, you can only execute a single query at a time in a prepared statement. You'll need to loop through the result set of your original query and execute them one at a time -- the following stored procedure should do the trick:

DELIMITER $$
CREATE DEFINER=`somebody`@`%` PROCEDURE `test_proc`()
BEGIN

DECLARE done INT DEFAULT 0;
DECLARE tname TEXT DEFAULT "";

DECLARE exec_cur CURSOR FOR
    SELECT table_name
    FROM information_schema.COLUMNS    
    WHERE column_name = 'edit_time' 
    AND table_schema = 'homestead'
    ;

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET done=1;

OPEN exec_cur;

exec_loop: LOOP
    FETCH exec_cur INTO tname;
    IF done THEN 
        LEAVE exec_loop; 
    END IF;

    SET @cmd = CONCAT("ALTER TABLE ", tname, " DROP edit_time;");

    PREPARE stmt FROM @cmd;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END LOOP exec_loop;

END$$
DELIMITER ;

...or just copy the output rows of your original query, paste them back into your client, and then run them all ;-)

@BerndBuffen yes, but you can loop over result rows in a stored procedure, creating and executing prepared statements as you go -- good point though, I'll edit as it may not have been obvious to OP — A C, May 26 '17 at 20:29

Is there a way in MySQL to execute a result set from information_schema

1 Answers1