各类数据库查询库表字段属性SQL

达梦：

SELECT
c.TABLE_NAME AS 表名,
c.COLUMN_NAME AS 字段名,
CASE WHEN k.COLUMN_NAME IS NOT NULL THEN ‘是’ ELSE ‘否’ END AS 是否主键,
c.DATA_TYPE AS 数据类型,
c.DATA_LENGTH AS 字节长度,
c.DATA_PRECISION AS 数值精度,
c.DATA_SCALE AS 小数位,
c.NULLABLE AS 可空,
c.DATA_DEFAULT AS 默认值,
col.COMMENTS AS 字段注释,
tab.COMMENTS AS 表注释
FROM USER_TAB_COLUMNS c
— 关联字段注释
LEFT JOIN USER_COL_COMMENTS col
ON c.TABLE_NAME = col.TABLE_NAME
AND c.COLUMN_NAME = col.COLUMN_NAME
— 关联表注释
LEFT JOIN USER_TAB_COMMENTS tab
ON c.TABLE_NAME = tab.TABLE_NAME
— 关联主键
LEFT JOIN USER_CONS_COLUMNS k
ON c.TABLE_NAME = k.TABLE_NAME
AND c.COLUMN_NAME = k.COLUMN_NAME
AND EXISTS (
SELECT 1 FROM USER_CONSTRAINTS con
WHERE con.CONSTRAINT_NAME = k.CONSTRAINT_NAME
AND con.CONSTRAINT_TYPE = ‘P’
)
ORDER BY c.TABLE_NAME, c.COLUMN_ID;

极简版（不需要主键，最稳定）
SELECT
c.TABLE_NAME AS 表名,
c.COLUMN_NAME AS 字段名,
c.DATA_TYPE AS 数据类型,
c.DATA_LENGTH AS 字节长度,
c.DATA_PRECISION AS 数值总精度,
c.DATA_SCALE AS 小数位数,
c.NULLABLE AS 是否允许空(Y/N),
c.DATA_DEFAULT AS 字段默认值,
col.COMMENTS AS 字段注释,
tab.COMMENTS AS 表注释
FROM USER_TAB_COLUMNS c
LEFT JOIN USER_COL_COMMENTS col
ON c.TABLE_NAME = col.TABLE_NAME
AND c.COLUMN_NAME = col.COLUMN_NAME
LEFT JOIN USER_TAB_COMMENTS tab
ON c.TABLE_NAME = tab.TABLE_NAME
ORDER BY c.TABLE_NAME, c.COLUMN_ID;

oracle数据库

SELECT
c.table_name 表名,
c.column_name 字段名,
c.data_type 数据类型,
c.data_length 字节长度,
c.data_precision 数值精度,
c.data_scale 小数位数,
c.nullable 是否为空,
c.data_default 默认值,
col_com.comments 字段注释,
tab_com.comments 表注释
FROM user_tab_columns c
LEFT JOIN user_col_comments col_com
ON c.table_name = col_com.table_name
AND c.column_name = col_com.column_name
LEFT JOIN user_tab_comments tab_com
ON c.table_name = tab_com.table_name
ORDER BY c.table_name, c.column_id;

OPENPG

SELECT
t.table_name AS 表名,
c.column_name AS 字段名,
CASE WHEN k.column_name IS NOT NULL THEN ‘是’ ELSE ‘否’ END AS 是否主键,
c.data_type AS 数据类型,
— 字符类型长度
c.character_maximum_length AS 字符长度,
— 数值精度/小数位
c.numeric_precision AS 数值总精度,
c.numeric_scale AS 小数位数,
CASE c.is_nullable WHEN ‘YES’ THEN ‘是’ ELSE ‘否’ END AS 是否允许空,
c.column_default AS 默认值,
col_desc.description AS 字段注释,
tab_desc.description AS 表注释
FROM information_schema.columns c
— 关联表基础信息
JOIN information_schema.tables t
ON c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND t.table_type = ‘BASE TABLE’ — 只查普通表，排除视图
— 字段注释
LEFT JOIN pg_description col_desc
ON col_desc.objoid = (c.table_schema || ‘.’ || c.table_name)::regclass
AND col_desc.objsubid = c.ordinal_position
— 表注释
LEFT JOIN pg_description tab_desc
ON tab_desc.objoid = (c.table_schema || ‘.’ || c.table_name)::regclass
AND tab_desc.objsubid = 0
— 主键关联
LEFT JOIN (
SELECT
tc.table_schema,
tc.table_name,
kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
WHERE tc.constraint_type = ‘PRIMARY KEY’
) k
ON c.table_schema = k.table_schema
AND c.table_name = k.table_name
AND c.column_name = k.column_name
WHERE c.table_schema = CURRENT_SCHEMA() — 当前模式
ORDER BY c.table_name, c.ordinal_position;