Mysql表结构查询

 

SELECT DB_Column.table_name AS '表名'
       ,DB_Table.table_comment AS '表说明'
       ,DB_Column.ORDINAL_POSITION AS '字段序号'
       ,DB_Column.COLUMN_NAME AS '字段名'
       ,DB_Column.COLUMN_COMMENT AS '字段说明'
       ,(CASE WHEN DB_Column.COLUMN_KEY ='PRI' THEN '√' ELSE '' END) AS '主键'
       ,DB_Column.DATA_TYPE AS '类型'
       ,DB_Column.CHARACTER_OCTET_LENGTH AS '占用字节数'
       ,(CASE WHEN DB_Column.CHARACTER_MAXIMUM_LENGTH IS NULL THEN DB_Column.NUMERIC_PRECISION ELSE DB_Column.CHARACTER_MAXIMUM_LENGTH  END )AS '长度'
       ,DB_Column.NUMERIC_SCALE AS '小数位数'
       ,(CASE WHEN DB_Column.IS_NULLABLE = 'YES' THEN '√' ELSE '' END ) AS '允许空'
       ,DB_Column.COLUMN_DEFAULT AS '默认值'       
  FROM information_schema.COLUMNS AS DB_Column 
    LEFT JOIN information_schema.tables AS DB_Table
      ON DB_Column.table_name=DB_Table.table_name
 WHERE DB_Column.table_schema = '库名称'  

执行效果如小图所示：