mysql数据字典生成_mysql生成数据字典

DELIMITER $$USE`mtapp_toms`$$DROP PROCEDURE IF EXISTS`proc_getdatadic`$$CREATE DEFINER=`root`@`%` PROCEDURE`proc_getdatadic`()BEGIN

DECLARE v_tablename VARCHAR(50);DECLARE v_columnname VARCHAR(50);DECLARE v_disname VARCHAR(50);DECLARE v_priname VARCHAR(50);DECLARE v_datatype VARCHAR(50);DECLARE v_datalen VARCHAR(50);DECLARE v_desc VARCHAR(50);DECLARE v_col VARCHAR(255);DECLARE v_tname VARCHAR(50);DECLARE v_count INT;DECLARE done INT DEFAULTFALSE;DECLARE cur CURSOR FOR

SELECT * FROM(SELECTtable_name 表名,

column_name 字段名,

column_name 显示名,IF(column_key='PRI','是','') 主键,data_type 数据类型,IF(character_maximum_length>0,character_maximum_length,'') 长度,IF(column_comment='',column_name,column_comment) 说明FROMinformation_schema.COLUMNSWHERE TABLE_SCHEMA = 'mtapp_toms'

AND table_name LIKE 'tb_用户管理%'

AND table_name NOT LIKE '%_mm_%'

AND table_name NOT LIKE '%_dd_%'

AND table_name NOT LIKE '%19%'

AND table_name NOT LIKE '%18%'

UNION ALL

SELECTtable_name 表名,

column_name 字段名,

column_name 显示名,IF(column_key='PRI','是','') 主键,data_type 数据类型,IF(character_maximum_length>0,character_maximum_length,'') 长度,IF(column_comment='',column_name,column_comment) 说明FROMinformation_schema.COLUMNSWHERE TABLE_SCHEMA = 'mtapp_toms'

AND (table_name LIKE 'tb_家宽%' OR table_name LIKE 'tb_集客%')AND table_name NOT LIKE '%_mm_%'

AND table_name NOT LIKE '%_dd_%'

AND table_name NOT LIKE '%19%'

AND table_name NOT LIKE '%18%'

UNION ALL

SELECTtable_name 表名,

column_name 字段名,

column_name 显示名,IF(column_key='PRI','是','') 主键,data_type 数据类型,IF(character_maximum_length>0,character_maximum_length,'') 长度,IF(column_comment='',column_name,column_comment) 说明FROMinformation_schema.COLUMNSWHERE TABLE_SCHEMA = 'mtapp_toms'

AND (table_name LIKE 'tb_退服%' OR table_name LIKE 'tb_动环%')AND table_name NOT LIKE '%_mm_%'

AND table_name NOT LIKE '%_dd_%'

AND table_name NOT LIKE '%19%'

AND table_name NOT LIKE '%18%'

UNION ALL

SELECTtable_name 表名,

column_name 字段名,

column_name 显示名,IF(column_key='PRI','是','') 主键,data_type 数据类型,IF(character_maximum_length>0,character_maximum_length,'') 长度,IF(column_comment='',column_name,column_comment) 说明FROMinformation_schema.COLUMNSWHERE TABLE_SCHEMA = 'mtapp_toms'

AND table_name LIKE 'tb_%'

AND table_name NOT LIKE 'tb_家宽%'

AND table_name NOT LIKE 'tb_集客%'

AND table_name NOT LIKE 'tb_用户管理%'

AND table_name NOT LIKE 'tb_退服%'

AND table_name NOT LIKE 'tb_动环%'

AND table_name NOT LIKE '%_mm_%'

AND table_name NOT LIKE '%_dd_%'

AND table_name NOT LIKE '%19%'

AND table_name NOT LIKE '%18%') t;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE;DROP TEMPORARY TABLE IF EXISTStemp_dic;CREATE TEMPORARY TABLEtemp_dic

(

colVARCHAR(255) NOT NULL);SET v_tname='';SET v_count=1;OPENcur;

read_loop: LOOPFETCH cur INTOv_tablename,v_columnname,v_disname,v_priname,v_datatype,v_datalen,v_desc;IF done THENLEAVE read_loop;END IF;IF(v_tablename!=v_tname) THEN

IF(v_count>1) THEN

INSERT INTO temp_dic VALUES('');END IF;INSERT INTO temp_dic VALUES(CONCAT('

',v_tablename,'

'));INSERT INTO temp_dic VALUES(CONCAT('

字段名	显示名	主键	数据类型	长度	说明
',v_columnname,'	',v_disname,'	',v_priname,'	',v_datatype,'	',v_datalen,'	',v_desc,'

INSERT INTO temp_dic VALUES('

');END IF;SELECT * FROMtemp_dic;END$$

DELIMITER ;