Skip to content

Instantly share code, notes, and snippets.

@carlosrobles
Last active December 6, 2022 18:53
Show Gist options
  • Save carlosrobles/e87fc652a0e266533c7598458961ee03 to your computer and use it in GitHub Desktop.
Save carlosrobles/e87fc652a0e266533c7598458961ee03 to your computer and use it in GitHub Desktop.
Add auto_increment to all the single PRIMARY KEY of type int of a database
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();
@w-log
Copy link

w-log commented Jul 15, 2020

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();

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment