selecta.table_name,a.column_name,a.NULLABLE,a.DATA_DEFAULT,a.data_type,b.CONSTRAINT_TYPE from
(
select table_name,column_name,NULLABLE,DATA_DEFAULT ,
case
when data_type='VARCHAR2' then data_type||'('||data_length||')'
when data_type='DATE' then data_type
when data_type='NUMBER' then data_type||'('||DATA_PRECISION||')'
when data_type='VARCHAR2' then data_type||'('||data_LENGTH||')'
when data_type='CHAR' then data_type||'('||data_LENGTH||')'
when data_type='CLOB' then data_type
when data_type='INTERGER' then data_type
end data_type
from all_tab_cols
where owner='OTAS' and table_name like 'T_%'
) a,
(
select c.table_name,d.column_name pk_column,c.CONSTRAINT_TYPE from all_constraints c,all_cons_columns d
where c.constraint_name=d.constraint_name and c.constraint_type'C'and c.table_name like 'T_%' and c.owner='OTAS'
) b
where a.table_name=b.table_name(+)and a.column_name=b.pk_column(+)
order by table_name;
发现没有integer
|