Add auto_increment to all the single PRIMARY KEY of type int of a database

add_auto_increment_to_int_single_pk.sql

DROP TABLE if exists temp;

CREATE TABLE temp (
  `table` varchar(250) DEFAULT NULL,
  `colum` varchar(250) DEFAULT NULL,
  `type` varchar(250) DEFAULT NULL,
  `number_of_PK` tinyint(2) DEFAULT NULL,
  `alter` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP PROCEDURE IF EXISTS foo;

DELIMITER //

CREATE PROCEDURE foo()

BEGIN

	declare tableName varchar(250);
	declare done int default 0;
	declare totalKeys int default 0;
	declare columnName varchar(250);
	declare columnType varchar(250);
	
  	#select all the #tables but exclude the ones generated by AED DMS
  	declare cur1 cursor for SELECT Distinct(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME not LIKE "awsdms_%";
	declare continue handler for not found set done=1;
    
    open cur1;
    
    igmLoop: loop

     	fetch cur1 into tableName;
        
        set @cnt = 0;
		#SElECT all the tables-colum-datatype
		SELECT `total`,`column_name_`, `data_type_` into totalKeys, columnName, columnType FROM (
			SELECT MAX(rowNumber) as total,  `column_type_`, `data_type_` , `column_name_` FROM (
				SELECT (@cnt := @cnt + 1) AS rowNumber, `column_name_`, `column_type_`, `data_type_` FROM (
					SELECT 
       					c.COLUMN_NAME as `column_name_`, cls.COLUMN_TYPE as `column_type_`, DATA_TYPE as `data_type_`
						FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p 
						JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  							ON  c.TABLE_NAME = p.TABLE_NAME
  							AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME
 						JOIN INFORMATION_SCHEMA.COLUMNS cls
  							ON  c.TABLE_NAME = cls.TABLE_NAME
  							AND c.COLUMN_NAME = cls.COLUMN_NAME
						WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
 						AND c.TABLE_NAME = tableName
 						group by c.COLUMN_NAME
					) a					
				) b
			) c;
						
		SET @t1 = CONCAT("ALTER table ", tableName," modify ", columnName," ", columnType," NOT NULL AUTO_INCREMENT");
		#log it
		insert into temp values (tableName, columnName, columnType, totalKeys, @t1 );
		if totalKeys = 1 and columnType like "%int%" then
		        #update the row
			PREPARE stmt3 FROM @t1;
 			EXECUTE stmt3;
 		end if; 
                
    if done = 1 then leave igmLoop; end if; 
        
    end loop igmLoop;
    
    close cur1;
    
  
END //

DELIMITER ; 

call foo();

The table is unique in the schema, so I think we should specify the schema in the query.
Below is my revised solution.

DROP TABLE	 if exists temp;

CREATE TABLE temp (
  `table` varchar(250) DEFAULT NULL,
  `colum` varchar(250) DEFAULT NULL,
  `type` varchar(250) DEFAULT NULL,
  `number_of_PK` tinyint(2) DEFAULT NULL,
  `alter` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP PROCEDURE IF EXISTS foo;

DELIMITER //

CREATE PROCEDURE foo()

BEGIN
	declare tableName varchar(250);
        #add Schema Type
        declare schemaType varchar(250);
	declare done int default 0;	
	declare totalKeys int default 0;
	declare columnName varchar(250);
	declare columnType varchar(250);
	
  	#select all the #tables but exclude the ones generated by AED DMS
  	declare cur1 cursor for SELECT Distinct(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME not LIKE "awsdms_%" AND (TABLE_SCHEMA not LIKE 'mysql' AND TABLE_SCHEMA not LIKE 'sys');
	declare continue handler for not found set done=1;
    
    open cur1;
    
    
    igmLoop: loop

     	fetch cur1 into tableName;
        
        
        set @cnt = 0;
		#SElECT all the tables-colum-datatype
		SELECT `total`,`column_name_`, `data_type_`, `schema_` into totalKeys, columnName, columnType, schemaType FROM (
			SELECT MAX(rowNumber) as total,  `column_type_`, `data_type_` , `column_name_`, `schema_` FROM (
				SELECT (@cnt := @cnt + 1) AS rowNumber, `column_name_`, `column_type_`, `data_type_`, `schema_` FROM (
					SELECT 
       					c.COLUMN_NAME as `column_name_`, cls.COLUMN_TYPE as `column_type_`, DATA_TYPE as `data_type_`,
                        p.TABLE_SCHEMA as `schema_`
						FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p 
						JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  							ON  c.TABLE_NAME = p.TABLE_NAME
  							AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME
 						JOIN INFORMATION_SCHEMA.COLUMNS cls
  							ON  c.TABLE_NAME = cls.TABLE_NAME
  							AND c.COLUMN_NAME = cls.COLUMN_NAME
						WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
 						AND c.TABLE_NAME = tableName
 						group by c.COLUMN_NAME
					) a					
				) b
			) c;
						
		SET @t1 = CONCAT("ALTER table ", schemaType, '.', tableName," modify ", columnName," ", columnType," NOT NULL AUTO_INCREMENT");
		#log it
		insert into temp values (tableName, columnName, columnType, totalKeys, @t1 );
		if totalKeys = 1 and columnType like "%int%" then
		        #update the row
			PREPARE stmt3 FROM @t1;
 			EXECUTE stmt3;
 		end if; 
                
    if done = 1 then leave igmLoop; end if; 
        
    end loop igmLoop;
    
    close cur1;
    
  
END //

DELIMITER ; 

call foo();