mysql数据查询表名和注释

• 查询所有表名

  • select table_name from information_schema.tables where table_schema='数据库名称';

• 查询数据库表名的注释:

  • select table_name 表名,TABLE_COMMENT 表注释 from INFORMATION_SCHEMA.TABLES Where table_schema = '数据库名称' AND table_name ="表名"

• 查询所有表名的表注释:

  • select table_name 表名,TABLE_COMMENT 表注释 from INFORMATION_SCHEMA.TABLES Where table_schema = '数据库名称' AND table_name IN (select table_name from information_schema.tables where table_schema='数据库名称'); 

• 查询指定数据的所有表字段 及注释等信息

  • SELECT
    a.table_name 表名,
    a.table_comment 表说明,
    b.COLUMN_NAME 字段名,
    b.column_comment 字段说明,
    b.column_type 字段类型,
    b.column_key 约束
    FROM
    information_schema. TABLES a
    LEFT JOIN information_schema.COLUMNS b ON a.table_name = b.TABLE_NAME
    WHERE
    a.table_schema = '数据库名称'
    ORDER BY
    a.table_name;

• 查询单表的所有的字段

  • select 't.',COLUMN_NAME,',' from information_schema.COLUMNS where table_name = 'cust_ackflow' and table_schema = 'salebatch';