xxC1NzwW

1drop procedure if exists add_all_timestamp;  
2CREATE PROCEDURE add_all_timestamp()  
3  BEGIN  
4    DECLARE tableName varchar(30) default '';  
5    DECLARE allAddr varchar(40) default '';
6    DECLARE tableSchemaName varchar(30) default 'test';
7    DECLARE done INT DEFAULT 0;  
8    DECLARE curl CURSOR FOR select table_name from information_schema.tables where table_schema=tableSchemaName;  
9    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
10    OPEN curl;  
11    REPEAT  
12      FETCH curl INTO tableName;  
13      IF not done THEN
14         IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=tableSchemaName AND table_name = tableName AND column_name = 'deleted_at') THEN
15           set @sql=concat('alter table ',tableName,' ADD  `deleted_at` TIMESTAMP NULL');
16           PREPARE stmt from @sql;
17           execute stmt;
18         END IF;
19         
20         IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=tableSchemaName AND table_name = tableName AND column_name = 'created_at') THEN
21           set @sql2=concat('alter table ',tableName,' ADD  `created_at` TIMESTAMP NULL');
22           PREPARE stmt2 from @sql2; 
23           execute stmt2;
24         END IF;
25				 
26				 IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=tableSchemaName AND table_name = tableName AND column_name = 'updated_at') THEN
27           set @sql3=concat('alter table ',tableName,' ADD  `updated_at` TIMESTAMP NULL');
28           PREPARE stmt3 from @sql3; 
29           execute stmt3;
30         END IF;
31         
32      END IF;  
33    UNTIL done END REPEAT;  
34    select allAddr;
35    CLOSE curl;     
36END;  
37
38call add_all_timestamp();