MySql 、Oracle 获取表结构和字段信息

1、MySql获取表结构信息

SELECT
    TABLE_NAME,
    TABLE_COMMENT
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = 'dm' -- dm 是数据库名称，需替换
ORDER BY
    TABLE_NAME;

 

2、MySql获取字段信息

SELECT
    TABLE_NAME AS 'tableName',
    COLUMN_NAME AS 'columnName',
    COLUMN_COMMENT AS 'columnComment',
    IS_NULLABLE AS 'nullable',
    DATA_TYPE AS 'dataType',
    CHARACTER_MAXIMUM_LENGTH AS 'strLength',
    NUMERIC_PRECISION AS 'numLength',
    NUMERIC_SCALE AS 'numBit'
FROM
    information_schema.`COLUMNS`
WHERE
    TABLE_SCHEMA = 'dm' -- dm 是数据库名称，需替换
AND TABLE_NAME = 'base_auth_resource' -- base_auth_resource 是表名，需替换
ORDER BY
    TABLE_NAME,
    ORDINAL_POSITION;

 

3、Oracle获取表结构信息

select t.table_name, c.COMMENTS
  from user_tables t, user_tab_comments c
 where t.TABLE_NAME = c.TABLE_NAME
 order by t.table_name

 

4、Oracle获取字段信息

select t.TABLE_NAME     AS tableName,
       t.COLUMN_NAME    AS columnName,
       c.COMMENTS       AS columnComment,
       t.NULLABLE       AS nullable,
       t.DATA_TYPE      AS dataType,
       t.CHAR_LENGTH    AS strLength,
       t.DATA_PRECISION AS numLength,
       t.DATA_SCALE     AS numBit
  from user_tab_columns t, user_col_comments c
 where t.TABLE_NAME = c.TABLE_NAME
   and t.COLUMN_NAME = c.COLUMN_NAME
   and t.TABLE_NAME = 'EMP' -- EMP 是表名，需替换
 order by t.TABLE_NAME, t.COLUMN_ID

 

注： 以上 sql 均为实际可运行代码，请注意将相关的“数据库名”或“表名”进行替换。