Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.