mysql 存储过程 多个表添加主键 自增

MySQL 多个表 添加主键 自增

传入数据库名 以id为主键

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`(in `dataName` VARCHAR(50))
begin
	DECLARE	taName VARCHAR(50) default '';
	DECLARE	`@sql2` VARCHAR(2048) default '';
	DECLARE	countNum int default 0;
	DECLARE	i int default 1;
	DECLARE	hasPrimaryNum int default 0;
	DROP TEMPORARY TABLE IF EXISTS tableName;
	CREATE TEMPORARY  TABLE tableName(id int primary key not null auto_increment,tableNames VARCHAR(50));
	#select databasesName;
	#查询数据库所有表名
	select dataName;
	insert tableName(tableNames) select table_name from information_schema.tables where table_schema = dataName;
	set countNum = found_rows();
		select *,countNum from tableName;
	#select countNum;
	#循环获取表名
	while i <= countNum do
	select tableNames into taName from tableName where id = i;
		select taName;
		#判断表是否有主键
		select count(*) FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='classes' AND constraint_name='PRIMARY' AND 		          CONSTRAINT_SCHEMA=dataName;
			select hasPrimaryNum;
			
		if hasPrimaryNum < 1 then
			#没有主键
			#添加主键 自增 id    自己选择合适的
		SET @sqlstr = CONCAT("alter table ",`taName`,"  change id id int not null auto_increment primary key");
		PREPARE stmt FROM @sqlstr;
		EXECUTE stmt;
		set @sqlstr='';
		end if;
	set i = i + 1;
	end while;
end