oracle常用查字段注释

select B.column_id , B.owner ,B.table_name , b.column_name,b.comments ,
case when B.data_type IN ('VARCHAR2','CHAR','NVARCHAR2','NCHAR' ) then B.data_type || '(' || B.CHAR_LENGTH || ')'
when B.data_type='NUMBER' THEN B.data_type || NVL2(B.DATA_PRECISION ,CASE WHEN B.data_scale='0' THEN '(' || B.DATA_PRECISION || ')' ELSE '(' || B.DATA_PRECISION || ','|| b.data_scale || ')' end ,'' )
else B.data_type end
as column_type from (
select a.column_id , a.owner, a.table_name, a.column_name ,c.COLUMN_NAME as colname , c.comments as comments ,a.data_type , a.CHAR_LENGTH,a.DATA_LENGTH,a.data_scale,a.DATA_PRECISION
from all_tab_columns a
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
where a.owner='按实际需要填约束' and
a.table_name in ('按实际需要填表名')
)B
order by B.owner,B.table_name,B.column_id asc

 

select
'CREATE TABLE ' || table_name || '(' || concat_column || ')' AS ODS FROM (
select table_name , substr ( concat_column , 1, length(concat_column)-1 ) as concat_column from (
select M.table_name ,xmlagg(xmlparse(content(M.column_name ||' '|| M.column_type )||','wellformed) order by M.column_id).getclobval() as concat_column
from
(
select B.column_id , B.owner ,B.table_name , b.column_name,b.comments ,
case when B.data_type IN ('VARCHAR2','CHAR','NVARCHAR2','NCHAR' ) then B.data_type || '(' || B.CHAR_LENGTH || ')'
when B.data_type='NUMBER' THEN B.data_type || NVL2(B.DATA_PRECISION ,CASE WHEN B.data_scale='0' THEN '(' || B.DATA_PRECISION || ')' ELSE '(' || B.DATA_PRECISION || ','|| b.data_scale || ')' end ,'' )
else B.data_type end
as column_type from (
select a.column_id , a.owner, a.table_name, a.column_name ,c.COLUMN_NAME as colname , c.comments as comments ,a.data_type , a.CHAR_LENGTH,a.DATA_LENGTH,a.DATA_PRECISION,a.data_scale
from all_tab_columns a
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
where a.owner='SCOTT' and
a.table_name in ('TEST_QUERY_SYSTEMDATE')
)B
order by B.owner,B.table_name,B.column_id asc
)M
group by M.table_name )
)