select s.name,st.name table_name,ac.name column_name,t.name column_type,ac.max_length,ac.precision,ac.scale,
case
when t.name=’sysname’ then ‘nvarchar2(128)’
when t.name=’nvarchar’ then ‘nvarchar2(‘+cast(ac.max_length as varchar)+’)’
when t.name=’varchar’ then ‘varchar2(‘+cast(ac.max_length as varchar)+’)’
when t.name=’char’ then ‘char(‘+cast(ac.max_length as varchar)+’)’
when t.name=’int’ then ‘number(10)’
when t.name=’bigint’ then ‘number(19)’
when t.name in (‘float’,’real’) then ‘number’
when t.name=’smallint’ then ‘number(5)’
when t.name=’tinyint’ then ‘number(3)’
when t.name=’bit’ then ‘number(1)’
when t.name=’datetime’ then ‘timestamp(3)’
when t.name=’datetime2′ then ‘timestamp(7)’
when t.name=’date’ then ‘date’
end oracle_type
from sys.all_columns ac join sys.types t on t.system_type_id=ac.system_type_id
join sys.tables st on st.object_id=ac.object_id
join sys.schemas s on s.schema_id=st.schema_id
where st.name!=’sysdiagrams’
order by s.name,st.name,ac.column_id