Oracle数据库获取表字段、注释、字段类型等

项目开发中我们经常遇到整理数据设计文档的场景，用这个脚本可以直接查询出来整理文档中我们需要的内容

with tmp as (
	select
		col.column_name,
        con.constraint_type
    from user_constraints con
    left join user_cons_columns col on con.constraint_name = col.constraint_name
    where con.table_name = ''
)
select 
	a.table_name,
    d.comments as table_comment,
    a.cloumn_name,
    b.comments as column_comment,
    case
		when a.data_type = 'VARCHAR2' then
			a.data_type || '(' a.data_length || ')'
		when a.data_type = 'NUMBER' then
			(case
				when a.data_precision is not null then
					a.data_type || '(' || a.data_precision || ',' || nvl(a.data_scale,0) || ')'
                end
			)
		else a.data_type
	end as data_type
	a.nullable,
    c.constraint_type
from 
	user_tab_columns a
	left join user_col_comments b on a.table_name = b.table_name and a.column_name = b.column_name
	left join tmp c on a.column_name = c.column_name
	left join user_tab_comments d on a.table_name = d.column_name
where 
	a.table_name = ''
order by 
	a.column_id