70sjWQ3D

1DROP TABLE if exists temp;
2
3CREATE TABLE temp (
4  `table` varchar(250) DEFAULT NULL,
5  `colum` varchar(250) DEFAULT NULL,
6  `type` varchar(250) DEFAULT NULL,
7  `number_of_PK` tinyint(2) DEFAULT NULL,
8  `alter` varchar(250) DEFAULT NULL
9) ENGINE=InnoDB DEFAULT CHARSET=latin1;
10
11DROP PROCEDURE IF EXISTS foo;
12
13DELIMITER //
14
15CREATE PROCEDURE foo()
16
17BEGIN
18
19	declare tableName varchar(250);
20	declare done int default 0;
21	declare totalKeys int default 0;
22	declare columnName varchar(250);
23	declare columnType varchar(250);
24	
25  	#select all the #tables but exclude the ones generated by AED DMS
26  	declare cur1 cursor for SELECT Distinct(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME not LIKE "awsdms_%";
27	declare continue handler for not found set done=1;
28    
29    open cur1;
30    
31    igmLoop: loop
32
33     	fetch cur1 into tableName;
34        
35        set @cnt = 0;
36		#SElECT all the tables-colum-datatype
37		SELECT `total`,`column_name_`, `data_type_` into totalKeys, columnName, columnType FROM (
38			SELECT MAX(rowNumber) as total,  `column_type_`, `data_type_` , `column_name_` FROM (
39				SELECT (@cnt := @cnt + 1) AS rowNumber, `column_name_`, `column_type_`, `data_type_` FROM (
40					SELECT 
41       					c.COLUMN_NAME as `column_name_`, cls.COLUMN_TYPE as `column_type_`, DATA_TYPE as `data_type_`
42						FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p 
43						JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
44  							ON  c.TABLE_NAME = p.TABLE_NAME
45  							AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME
46 						JOIN INFORMATION_SCHEMA.COLUMNS cls
47  							ON  c.TABLE_NAME = cls.TABLE_NAME
48  							AND c.COLUMN_NAME = cls.COLUMN_NAME
49						WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
50 						AND c.TABLE_NAME = tableName
51 						group by c.COLUMN_NAME
52					) a					
53				) b
54			) c;
55						
56		SET @t1 = CONCAT("ALTER table ", tableName," modify ", columnName," ", columnType," NOT NULL AUTO_INCREMENT");
57		#log it
58		insert into temp values (tableName, columnName, columnType, totalKeys, @t1 );
59		if totalKeys = 1 and columnType like "%int%" then
60		        #update the row
61			PREPARE stmt3 FROM @t1;
62 			EXECUTE stmt3;
63 		end if; 
64                
65    if done = 1 then leave igmLoop; end if; 
66        
67    end loop igmLoop;
68    
69    close cur1;
70    
71  
72END //
73
74DELIMITER ; 
75
76call foo();