CREATE DEFINER=root@localhost PROCEDURE insert_defaults(IN table_name VARCHAR(50)) BEGIN DECLARE col_name VARCHAR(50); DECLARE col_type VARCHAR(50); DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT column_name, data_type FROM information_schema.columns WHERE table_name = table_name; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur;

read_loop: LOOP FETCH cur INTO col_name, col_type; IF done THEN LEAVE read_loop; END IF;

SET @default = NULL; SET @q1 = CONCAT('SELECT IFNULL(MAX(', col_name, '), NULL) INTO @max FROM ', table_name); PREPARE stmt1 FROM @q1; EXECUTE stmt1; DEALLOCATE PREPARE stmt1;

IF col_type LIKE '%char%' THEN SET @default = 'default string'; ELSEIF col_type LIKE '%int%' THEN SET @default = '0'; ELSEIF col_type LIKE '%date%' THEN SET @default = '1900-01-01'; END IF;

IF @max IS NULL THEN SET @q2 = CONCAT('ALTER TABLE ', table_name, ' MODIFY COLUMN ', col_name, ' ', col_type, ' DEFAULT ''', @default, ''''); ELSE SET @q2 = CONCAT('ALTER TABLE ', table_name, ' MODIFY COLUMN ', col_name, ' ', col_type, ' DEFAULT NULL'); END IF;

PREPARE stmt2 FROM @q2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2;

END LOOP;

CLOSE cur; END