mysql使用sql语句查询数据库所有表注释等
2018-08-14 11:26 通往神之路 阅读(1770) 评论(0) 编辑 收藏 举报1、/* 查询数据库 test 所有表注释 */
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='test';
2、要查询表字段的注释
SELECT COLUMN_NAME,column_comment FROM INFORMATION_SCHEMA.Columns WHERE table_name='class' AND table_schema='test';
3、一次性查询数据库 "test" 下表注释以及对应表字段注释
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`='test';
4、项目中使用的,查询数据库所有表的相关字段,属性
SELECT 'xx系统' system_name, table_comment table_comment, a.table_name, column_comment, column_name, CASE WHEN column_key ='PRI' THEN 'Y' ELSE 'N' END is_key, CASE WHEN column_name='id' OR column_name='created_by' THEN '自动' ELSE '手动' END get_type, column_type FROM (SELECT * FROM information_schema.COLUMNS WHERE table_schema ='test')a LEFT JOIN (SELECT table_name,table_comment FROM information_schema.TABLES WHERE table_schema='test') b ON a.table_name = b.table_name ORDER BY table_name,ordinal_position ;