####获取所有表及注释
CREATE PROCEDURE `getTableColumns`() BEGIN DECLARE _table_name ,_table_comment,_column_name ,_column_type,_column_comment varchar(255) DEFAULT ''; DECLARE i int DEFAULT 1; DECLARE _fl INT DEFAULT 0; DECLARE cursor_fl CURSOR FOR select table_name,table_comment from information_schema.tables where table_schema='consumer_sit'; DECLARE continue HANDLER for not found set _fl= 1; TRUNCATE table temp_table_column; OPEN cursor_fl; #启动 fetch cursor_fl INTO _table_name ,_table_comment; WHILE _fl<> 1 do INSERT INTO temp_table_column(`column_name` ,`column_type`) VALUES(i ,_table_name); INSERT INTO temp_table_column(`column_name` ,`column_type`) VALUES('描述' ,_table_comment); INSERT INTO temp_table_column(`column_name` ,`column_type`,`column_comment`) VALUES('字段' ,'字段类型','备注'); call findComment(_table_name,_table_comment); INSERT INTO temp_table_column(`table_name` ,`table_comment`,`column_name` ,`column_type`,`column_comment` ) VALUES('' ,'','','',''); fetch cursor_fl INTO _table_name ,_table_comment; SET i=i+1; commit; END WHILE; CLOSE cursor_fl; SELECT * FROM temp_table_column ; END####获取字段注释
CREATE PROCEDURE `findComment`(IN _table_name varchar(50),_table_comment varchar(255)) BEGIN DECLARE _column_name ,_column_type,_column_comment varchar(255) DEFAULT ''; DECLARE _fl INT DEFAULT 0; DECLARE cursor_fl CURSOR FOR SELECT COLUMN_NAME ,COLUMN_TYPE ,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema ='consumer_sit' AND table_name =_table_name; DECLARE continue HANDLER for not found set _fl= 1; OPEN cursor_fl; #启动 fetch cursor_fl INTO _column_name ,_column_type,_column_comment; WHILE _fl<> 1 do INSERT INTO temp_table_column(`table_name` ,`table_comment`,`column_name` ,`column_type`,`column_comment` ) VALUES(_table_name ,_table_comment,_column_name , _column_type,_column_comment); fetch cursor_fl INTO _column_name ,_column_type,_column_comment; commit; END WHILE; CLOSE cursor_fl; END#临时表
CREATE TABLE temp_table_column ( `id` int(11) NOT NULL AUTO_INCREMENT, `table_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `table_comment` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `column_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `column_type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `column_comment` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2125 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;##查询结果
select column_name 字段,column_type 字段类型 ,column_comment 备注 from temp_table_column；
