Oracle表字段和备注信息获取
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'
GROUP BY
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
where 1=1
and a.OWNER='test'
and a.TABLE_NAME in ('tablenames')
and a.COLUMN_ID is not null
and a.COLUMN_NAME not in ('name','id')
order by 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 t1
join 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')
order by 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'
