MySql/SQL Server/DM/PolarDB-PG查询数据库名称、表名、表注释、字段名、字段注释、字段类型、是否为空。

查询各个数据库的表结构信息：数据库名称、表名、表注释、字段名、字段注释、字段类型、是否为空。

MySql

select isc.TABLE_SCHEMA,    
       isc.TABLE_NAME,
       ist.TABLE_COMMENT,
       isc.COLUMN_NAME,
       isc.COLUMN_COMMENT,
       isc.COLUMN_TYPE,
       IS_NULLABLE
from information_schema.columns isc
         INNER JOIN information_schema.tables ist on isc.TABLE_NAME = ist.TABLE_NAME and
                                                     isc.TABLE_SCHEMA = ist.TABLE_SCHEMA
where isc.TABLE_SCHEMA in ('数据库名');

SQL Server

USE 数据库名;
GO
SELECT DB_NAME()                        as                                 TABLE_SCHEMA,
       CONVERT(VARCHAR (50), d.name)    as                                 TABLE_NAME,
       CONVERT(VARCHAR (50), f.value)   as                                 TABLE_COMMENT,
       CONVERT(VARCHAR (50), a.name)    as                                 COLUMN_NAME,
       CONVERT(VARCHAR (50), g.[value]) as                                 COLUMN_COMMENT,
       CONCAT(b.name, '(', COLUMNPROPERTY(a.id, a.name, 'PRECISION'), ')') COLUMN_TYPE,
       (
           CASE WHEN a.isnullable = 1 THEN 'Y' ELSE 'N' END
           )                                                               IS_NULLABLE
FROM syscolumns a
         LEFT JOIN systypes b ON a.xtype = b.xusertype
         INNER JOIN sysobjects d ON a.id = d.id
    AND d.xtype = 'U'
    AND d.name <> 'dtproperties'
         LEFT JOIN syscomments e ON a.cdefault = e.id
         LEFT JOIN sys.extended_properties g ON a.id = g.major_id
    AND a.colid = g.minor_id
         LEFT JOIN sys.extended_properties f on d.id = f.major_id
    and f.minor_id = 0 

DM达梦

SELECT c.owner                                      AS TABLE_SCHEMA,
       c.table_name                                 AS TABLE_NAME,
       b.comments                                   AS TABLE_COMMENT,
       c.column_name                                AS COLUMN_NAME,
       a.comments                                   AS COLUMN_COMMENT,
       CONCAT(c.data_type, '(', c.data_length, ')') AS COLUMN_TYPE,
       c.nullable                                   AS IS_NULLABLE
FROM user_col_comments a
         INNER JOIN user_tab_comments b ON a.table_name = b.table_name
         INNER JOIN all_tab_columns c ON c.table_name = a.table_name AND a.COLUMN_NAME = c.COLUMN_NAME
WHERE c.owner = '数据库名';

PolarDB for PostgreSQL

SELECT isc.table_catalog                                                                       as "TABLE_SCHEMA",
       isc.table_name                                                                          as "TABLE_NAME",
       obj_description(c.oid)                                                                  as "TABLE_COMMENT",
       isc.column_name                                                                         as "COLUMN_NAME",
       c.description                                                                           as "COLUMN_COMMENT",
       concat_ws('', c.typname, SUBSTRING(format_type(c.atttypid, c.atttypmod) from '\(.*\)')) as "COLUMN_TYPE",
       isc.is_nullable                                                                         as "IS_NULLABLE"
FROM information_schema.columns isc
         join(select t.typname,
                     a.atttypid,
                     a.atttypmod,
                     c.relname,
                     c.oid,
                     d.description
              from pg_class c,
                   pg_attribute a,
                   pg_type t,
                   pg_description d
              where a.attnum > 0
                and a.attrelid = c.oid
                and a.atttypid = t.oid
                and d.objoid = a.attrelid
                and d.objsubid = a.attnum
                and c.relname in (select tablename
                                  from pg_tables
                                  where schemaname = 'public'
                                    and position('_2' in tablename) = 0)
              order by c.relname, a.attnum) c on isc.table_name = c.relname
WHERE table_catalog = '数据库名'
  and table_schema = 'public';

以上内容均为自己整理，可能存在一些问题或更简单的方法查询，希望可以多多沟通。