MySQL 表信息查询，便于补数据库结构设计文档

MySQL 表信息查询，便于补数据库结构设计文档

select c.table_name                                          表名,
       t.TABLE_COMMENT                                       表说明,
       c.COLUMN_NAME                                         列名,
       c.COLUMN_TYPE                                         数据类型,
       case c.COLUMN_KEY when 'PRI' then '是' else '' end    主键,
       case c.is_nullable when 'YES' then '是' else '否' end 是否非空,
       c.COLUMN_COMMENT                                      注释
from information_schema.`COLUMNS` c,
     information_schema.TABLES t
where c.TABLE_SCHEMA = '数据库名'
  and c.TABLE_SCHEMA = t.TABLE_SCHEMA
  and c.TABLE_NAME = t.TABLE_NAME
  and c.TABLE_NAME in ('表名')
order by c.table_name asc, (case c.COLUMN_KEY when 'PRI' then 1 else 0 end) desc, c.COLUMN_NAME asc;

ORACLE 表字段信息查询

SELECT c1.TABLE_NAME as table_name_en, c3.COMMENTS as table_name_zh, c1.COLUMN_NAME, c2.COMMENTS
FROM user_tab_columns c1
         LEFT JOIN user_col_comments c2 ON c1.TABLE_NAME = c2.TABLE_NAME AND c1.COLUMN_NAME = c2.COLUMN_NAME
    left join USER_TAB_COMMENTS c3 on c1.TABLE_NAME = c3.TABLE_NAME
WHERE c1.TABLE_NAME = '表明'
  and c1.COLUMN_NAME in (
                         '字段名')
order by c2.comments;