//增加一个库下面所有表的row_id和其他9个字段的存过
DELIMITER $$
USE `erptest`$$
DROP PROCEDURE IF EXISTS `UPTABLE`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UPTABLE`()
BEGIN
declare _tableName varchar(50);
declare _done int default 0;
declare _cur cursor for
select table_name from information_schema.`COLUMNS` where table_schema = 'erptest'(库名) group by table_name;
declare continue handler for sqlstate '02000' set _done=1;
open _cur;
repeat
fetch _cur into _tableName;
if not _done then
begin
if not exists(select * from information_schema.`COLUMNS` where table_schema = 'erptest' and table_name = _tableName and column_name='EXT_Row_Id')then
set @stmt := concat("alter table ",_tableName,"
add EXT_Row_Id varchar(32) primary key not null comment '主键,不为空',
add EXT_Date_Time datetime comment '时间戳 YYYY-MM-DD HH:MM:SS',
add EXT_Ogg_Seq bigint not null comment '自增序列',
add EXT_flag varchar(10) comment '操作标识',
add EXT_Src_System varchar(100) not null comment '来源系统',
add EXT_Valid_Flag varchar(1) default '1' not null comment '有效标识',
add EXT_Provincial_Flag varchar(5) comment '来源单位',
add EXT_Reserve1 varchar(100) comment '预留字段1',
add EXT_Reserve2 varchar(100) comment '预留字段2',
add EXT_Reserve3 varchar(100) comment '预留字段3';");
prepare stmt from @stmt;
execute stmt;
end if;
end;
end if;
until _done end repeat;
close _cur;
set _done = 0;
END$$
DELIMITER ;
//删除一个mysql库下面所有有主键的表中的主键存过
DELIMITER $$
USE `erptest`$$
DROP PROCEDURE IF EXISTS `dropkey`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dropkey`()
BEGIN
declare _tableName varchar(50);
declare _done int default 0;
declare _cur cursor for
select table_name from information_schema.`KEY_COLUMN_USAGE` where table_schema = 'erptest' group by table_name;
declare continue handler for sqlstate '02000' set _done=1;
open _cur;
repeat
fetch _cur into _tableName;
if not _done then
begin
set @stmt := concat("alter table ",_tableName," drop primary key;");
prepare stmt from @stmt;
execute stmt;
end;
end if;
until _done end repeat;
close _cur;
set _done = 0;
END$$
DELIMITER ;