Oracle to SQL Server create table statements

This is an example to generate SQL Server create table statements from an Oracle database.

select 'create table '|| owner ||'.[' || table_name || '] (' as sql_table_name
  , LISTAGG('[' || column_name || '] '
    || CASE 
        WHEN data_type='BLOB' THEN ''
        WHEN data_type='DATE' THEN 'datetime'
        WHEN data_type='FLOAT' THEN ''
        WHEN data_type='NUMBER' THEN 'decimal('||nvl(data_precision,38)||','||nvl(data_scale,0)||')'
        WHEN data_type like 'TIMESTAMP%' THEN 'timestamp'
        WHEN data_type='CLOB' THEN 'nvarchar(max)'
        WHEN data_type='CHAR' THEN 'nvarchar(' ||data_length|| ')'
        WHEN data_type='NVARCHAR2' THEN 'nvarchar(' ||data_length|| ')'
        WHEN data_type='VARCHAR2' THEN 'nvarchar(' ||data_length|| ')'
        ELSE '?'
        END
      ,', ') WITHIN GROUP( ORDER BY column_id ) AS sql_all_columns
      , ');' as sql_close
from all_tab_columns
where owner='SCHEMA'
--	and table_name in ('','')
group by owner, table_name
order by owner, table_name
;

See also

• Oracle to SQL Server create table statements