pg、mysql拉取表、表字段注释

最新推荐文章于 2025-05-26 15:40:22 发布

原创最新推荐文章于 2025-05-26 15:40:22 发布 · 697 阅读

0 ·

CC 4.0 BY-SA版权

DBA 专栏收录该内容

11 篇文章

订阅专栏

本文介绍如何使用SQL语句从pg和mysql数据库中拉取表字段的注释信息，包括字段名、类型、是否允许为空及表注释，适用于数据库管理和维护。

--pg拉取表字段注释
SELECT
C.relname,
col_description (A .attrelid, A .attnum) AS COMMENT,
   format_type (A .atttypid, A .atttypmod) AS TYPE,
   A .attname AS NAME,
   A .attnotnull AS NOTNULL
FROM
   pg_class AS C,
   pg_attribute AS A,
   pg_tables T
WHERE
A .attrelid = C .oid AND T.tablename=C.relname
AND A .attnum > 0 and T.schemaname = 'markting'

--mysql拉取表字段注释
SELECT table_schema,table_name,COLUMN_NAME,column_comment
FROM INFORMATION_SCHEMA.Columns
WHERE table_schema in ('schema1','schema2','schema3') and COLUMN_COMMENT = ''

--mysql拉取所有表注释
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='mammothcode';