select
a.OWNER as table_schema,
a.TABLE_NAME as table_name,
d.COMMENTS as tale_comments,
a.COLUMN_NAME as column_name,
a.COLUMN_ID as column_id,
c.COMMENTS as col_comments,
a.DATA_TYPE as data_type,
a.DATA_PRECISION as data_len,
a.CHAR_LENGTH as char_len,
a.DATA_SCALE as data_scale,
DBMS_LOB.substr(b.constraint_type,100,1) as constraint_type
from ALL_TAB_COLS a
left join (
SELECT
ucc.table_name AS table_name,
ucc.column_name AS column_name,
wm_concat(uc.constraint_type) AS constraint_type
FROM
all_cons_columns ucc
LEFT JOIN all_constraints uc on ucc.constraint_name = uc.constraint_name
where ucc.OWNER='%s'GROUPBY
ucc.table_name,
ucc.column_name
) b on a.TABLE_NAME=b.table_name and a.COLUMN_NAME=b.column_name
LEFT JOIN all_col_comments c on a.OWNER=c.OWNER and a.TABLE_NAME=c.TABLE_NAME and a.COLUMN_NAME=c.COLUMN_NAME
left join all_TAB_COMMENTS d on a.OWNER=d.OWNER and a.TABLE_NAME=d.TABLE_NAME
where1=1and a.OWNER='test'and a.TABLE_NAME in ('tablenames')and a.COLUMN_ID isnot null
and a.COLUMN_NAME notin ('name','id')orderby a.COLUMN_ID asc
Postgrep表字段和备注信息获取
select
t7.nspname table_schema,
t1.relname table_name,
t4.description tale_comments,
t2.attname column_name,
t2.attnum column_id,
t3.description col_comments,
t5.typname data_type,
t2.attlen data_len,
0 data_scale,
t2.atttypmod char_len,
t6.contype constraint_type
from pg_class t1join pg_attribute t2 on t1.oid = t2.attrelid
left join pg_description t3 on t2.attnum = t3.objsubid and t2.attrelid = t3.objoid
left join pg_description t4 on t1.oid = t4.objoid and t4.objsubid = 0
left join pg_type t5 on t2.atttypid = t5.OID
left join pg_constraint t6 on t1.oid = t6.conrelid and t2.attnum = t6.conkey[1]
join pg_namespace t7 on t1.relnamespace = t7.oid
where t7.nspname = 'public'and t1.relname in ( 'table_rpt_list' )
and t2.attnum > 0
Mysql表字段和备注信息获取
SELECT
a.TABLE_SCHEMA as table_schema,
a.TABLE_NAME as table_name,
b.TABLE_COMMENT as tale_comments,
a.COLUMN_NAME as column_name,
a.ORDINAL_POSITION as column_id,
a.COLUMN_COMMENT as col_comments,
a.DATA_TYPE as data_type,
a.NUMERIC_PRECISION as data_len,
a.NUMERIC_SCALE as data_scale,
a.CHARACTER_MAXIMUM_LENGTH as char_len,
a.COLUMN_KEY as constraint_type
FROM INFORMATION_SCHEMA.COLUMNS a
left join information_schema.TABLES b on a.TABLE_SCHEMA=b.TABLE_SCHEMA and a.TABLE_NAME=b.TABLE_NAME
WHERE
a.TABLE_SCHEMA = 'public'AND a.TABLE_NAME in ('tablenames')orderby a.ORDINAL_POSITION asc
SqlServer表字段和备注信息获取
SELECT
a.TABLE_NAME as table_name,
'' as tale_comments,
a.COLUMN_NAME as column_name,
a.ORDINAL_POSITION as column_id,
'' as col_comments,
a.DATA_TYPE as data_type,
a.NUMERIC_PRECISION as data_len,
a.NUMERIC_SCALE as data_scale,
a.CHARACTER_MAXIMUM_LENGTH as char_len,
c.CONSTRAINT_TYPE as constraint_type
FROM INFORMATION_SCHEMA.COLUMNS a
left join INFORMATION_SCHEMA.TABLES b
on a.TABLE_CATALOG=b.TABLE_CATALOG and a.TABLE_SCHEMA=b.TABLE_SCHEMA and a.TABLE_NAME=b.TABLE_NAME
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
on a.TABLE_CATALOG=c.TABLE_CATALOG and a.TABLE_SCHEMA=c.TABLE_SCHEMA and a.TABLE_NAME=c.TABLE_NAME
WHERE
a.TABLE_CATALOG='tables'and a.TABLE_SCHEMA='dbo'
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了