coding++ ：MySQL 使用 SQL 语句查询数据库所有表注释已经表字段注释

1、要查询数据库 "mammothcode" 下所有表名以及表注释

/* 查询数据库 ‘mammothcode’ 所有表注释 */ SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='mammothcode';

2、要查询表字段的注释

/* 查询数据库 ‘mammothcode’ 下表 ‘t_adminuser’ 所有字段注释 */ SELECT COLUMN_NAME,column_comment FROM INFORMATION_SCHEMA.Columns WHERE table_name='t_adminuser' AND table_schema='mammothcode'

3、一次性查询数据库 "mammothcode" 下表注释以及对应表字段注释

SELECT t.TABLE_NAME,t.TABLE_COMMENT,c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT FROM information_schema.TABLES t,INFORMATION_SCHEMA.Columns c WHERE c.TABLE_NAME=t.TABLE_NAME AND t.`TABLE_SCHEMA`='mammothcode'

扩展：使用存储过程，对上面sql语句进行存储，SQL 如下：

DELIMITER//  
DROP PROCEDURE IF EXISTS findComment// CREATE PROCEDURE findComment (dbName VARCHAR(50)) BEGIN DECLARE stmt VARCHAR(65535); #如果用户名长度大于0 IF LENGTH(dbName)>0 THEN BEGIN SET @sqlstr=CONCAT('SELECT t.TABLE_NAME,t.TABLE_COMMENT,c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT FROM information_schema.TABLES t,INFORMATION_SCHEMA.Columns c WHERE c.TABLE_NAME=t.TABLE_NAME AND t.`TABLE_SCHEMA`=','''',dbName,''''); END; ELSE BEGIN SET @sqlstr=CONCAT('SELECT ','''','数据库名不能为空','''', 'AS ','''','提示',''''); END; END IF; PREPARE stmt FROM @sqlstr; EXECUTE stmt; END// DELIMITER ;

调用存储过程：

CALL findComment('xiyinli_test');

上面的存储过程还可以简化：在存储过程中直接查询当前使用的数据库，如下：

DELIMITER//  
DROP PROCEDURE IF EXISTS findComment// CREATE PROCEDURE findComment () BEGIN DECLARE stmt VARCHAR(65535); #查询当前的 use-->database SET @dbName=(SELECT DATABASE()); BEGIN SET @sqlstr=CONCAT('SELECT t.TABLE_NAME,t.TABLE_COMMENT,c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT FROM information_schema.TABLES t,INFORMATION_SCHEMA.Columns c WHERE c.TABLE_NAME=t.TABLE_NAME AND t.`TABLE_SCHEMA`=','''',@dbName,''''); END; PREPARE stmt FROM @sqlstr; EXECUTE stmt; END// DELIMITER ;

调用：

CALL findComment();

 

Face your past without regret. Handle your present with confidence.Prepare for future without fear. keep the faith and drop the fear...