Sybase生成oracle建表语句

单个表

--单个表
declare @pre varchar(10),@schema varchar(20), @tablename varchar(30), @tablespace varchar(30), @role1 varchar(100), @role2 varchar(100)
set @pre            =''                  
set @schema         =''             --
set @tablename      ='tblMDRT_Report'
set @tablespace     =''  --
set @role1          =''          --DNA_APP_STG
set @role2          =''     --
select case when a.colid=1 then 'create table '+@schema+'.'+ @pre +'_' + b.name + '(
' else null end
+ a.name 
+ case when char_length(a.name) <= 3 then '							' 
		when char_length(a.name) between 4 and 7 then 	'						' 
		when char_length(a.name) between 8 and 11 then  '					' 
		when char_length(a.name) between 12 and 15 then '				'
		when char_length(a.name) between 16 and 19 then '			'
		when char_length(a.name) between 20 and 23 then '		'
		else '	' end
+ case when c.name = 'bigint' then 'number(19)'
        when c.name = 'binary' then 'raw' + '(' + cast(a.length as varchar) + ')'
        when c.name = 'bit' then 'number(3)'
        when c.name = 'char' then 'char' + '(' + cast(a.length as varchar) + ')'
        when c.name = 'datetime' then 'timestamp(6)'
        when c.name = 'decimal' then 'number' + '(' + cast(a.prec as varchar) + ',' + cast(a.scale as varchar) + ')'
        when c.name = 'float' then 'number(38,18)'
        when c.name = 'image' then 'long raw'
        when c.name = 'int' then 'number(10,0)'
        when c.name = 'money' then 'number(38,18)'
        when c.name = 'nchar' then 'nchar' + '(' + cast(a.length as varchar) + ')'
        when c.name = 'numeric' then 'number' + '(' + cast(a.prec as varchar) + ',' + cast(a.scale as varchar) + ')'
        when c.name = 'numeric identity' then 'number' + '(' + cast(a.prec as varchar) + ',' + cast(a.scale as varchar) + ')'
        when c.name = 'nvarchar' then 'nvarchar2' + '(' + cast(a.length as varchar) + ')'
        when c.name = 'real' then 'float(24)'
        when c.name = 'smalldatetime' then 'date'
        when c.name = 'smallint' then 'number(5,0)'
        when c.name = 'samllmoney' then 'number' + '(' + cast(a.prec as varchar) + ',' + cast(a.scale as varchar) + ')'
        when c.name = 'text' then 'long'
        when c.name = 'date' then 'date'
        when c.name = 'timestamp' then 'raw(8)'
        when c.name = 'tinyint' then 'number(3,0)'
        when c.name = 'varbinary' then 'raw' + '(' + cast(a.length as varchar) + ')'
        when c.name = 'varchar' then 'varchar2' + '(' + cast(a.length as varchar) + ')'
  else + c.name + '(' + cast(a.prec as varchar) + ',' + cast(a.scale as varchar) + ')' 
  end
+ case when a.colid=tmp.max_id then ',' + char(10) + 'primary key()'+ char(10) +')' + char(10) +'TABLESPACE '+ @tablespace +';' 
  + char(10) +'GRANT SELECT,INSERT,UPDATE,DELETE ON '+ @schema+ '.'+ @pre +'_'+b.name+ ' TO '+ @role1 +';'
--  + char(10) +'GRANT SELECT ON '+@schema+'.'+ @pre +'_' +b.name+ ' TO '+ @role2 +';'
else ',' end  

AS SQL_TEXT

from  syscolumns a,sysobjects b,systypes c,
(select max(a.colid) max_id
from syscolumns a,sysobjects b
where b.type='U'
and a.id=b.id 
and upper(b.name)=upper(@tablename)) tmp 
where a.id=b.id 
and a.usertype=c.usertype 
and upper(b.name)=upper(@tablename)
order by a.colid

多个表,分两步执行

--多个表,分两步执行
DECLARE  table_nm_cursor  CURSOR 
    FOR SELECT '','','','', '', name 
	      from sysobjects 
		 where upper(name) in (
'TBLMDRT_REPORT'
,'TBL238S_TARGET'
,'TBL238_ACH_HISTORY'
,'TBL238_ACTIVITY'
,'TBL238_AY_2_LIFEPA'
,'TBL238_AY_2_PA'
,'TBL238_AY_3_ILB'
,'TBL238_AY_3_LIFEPA'
,'TBL238_AY_3_PA'
,'TBL238_AY_3_UVL'
,'TBL238_BANCA_2_LIFEPA'
,'TBL238_BANCA_3_LIFEPA'
,'TBL238_BANK_2_LIFEPA'
,'TBL238_BANK_3_ILB'
,'TBL238_BANK_3_LIFEPA'
,'TBL238_BANK_3_PA'
,'TBL238_BANK_3_UVL'
,'TBL238_BANK_LKUP'
,'TBL238_BK_2_LIFEPA'
,'TBL238_BK_3_LIFEPA'
,'TBL238_CONFIG'
,'TBL238_DAILY'
)

declare @pre varchar(10),@schema varchar(20), @tablename varchar(30), @tablespace varchar(30), @role1 varchar(100), @role2 varchar(100)
  
OPEN  table_nm_cursor
FETCH next table_nm_cursor INTO @pre,@schema,@tablespace,@role1,@role2,@tablename

while @@SQLSTATUS = 0 
begin
    select case when a.colid=1 then 'create table '+@schema+'.'+ @pre +'_' + b.name + '(
    ' else null end
    + a.name 
    + case when char_length(a.name) <= 3 then '							' 
    		when char_length(a.name) between 4 and 7 then 	'						' 
    		when char_length(a.name) between 8 and 11 then  '					' 
    		when char_length(a.name) between 12 and 15 then '				'
    		when char_length(a.name) between 16 and 19 then '			'
    		when char_length(a.name) between 20 and 23 then '		'
    		else '	' end
    + case when c.name = 'bigint' then 'number(19)'
            when c.name = 'binary' then 'raw' + '(' + cast(a.length as varchar) + ')'
            when c.name = 'bit' then 'number(3)'
            when c.name = 'char' then 'char' + '(' + cast(a.length as varchar) + ')'
            when c.name = 'datetime' then 'timestamp(6)'
            when c.name = 'decimal' then 'number' + '(' + cast(a.prec as varchar) + ',' + cast(a.scale as varchar) + ')'
            when c.name = 'float' then 'number(38,18)'
            when c.name = 'image' then 'long raw'
            when c.name = 'int' then 'number(10,0)'
            when c.name = 'money' then 'number(38,18)'
            when c.name = 'nchar' then 'nchar' + '(' + cast(a.length as varchar) + ')'
            when c.name = 'numeric' then 'number' + '(' + cast(a.prec as varchar) + ',' + cast(a.scale as varchar) + ')'
            when c.name = 'numeric identity' then 'number' + '(' + cast(a.prec as varchar) + ',' + cast(a.scale as varchar) + ')'
            when c.name = 'nvarchar' then 'nvarchar2' + '(' + cast(a.length as varchar) + ')'
            when c.name = 'real' then 'float(24)'
            when c.name = 'smalldatetime' then 'date'
            when c.name = 'smallint' then 'number(5,0)'
            when c.name = 'samllmoney' then 'number' + '(' + cast(a.prec as varchar) + ',' + cast(a.scale as varchar) + ')'
            when c.name = 'text' then 'long'
            when c.name = 'date' then 'date'
            when c.name = 'timestamp' then 'raw(8)'
            when c.name = 'tinyint' then 'number(3,0)'
            when c.name = 'varbinary' then 'raw' + '(' + cast(a.length as varchar) + ')'
            when c.name = 'varchar' then 'varchar2' + '(' + cast(a.length as varchar) + ')'
      else + c.name + '(' + cast(a.prec as varchar) + ',' + cast(a.scale as varchar) + ')' 
      end
    + case when a.colid=tmp.max_id then ',' + char(10) + 'primary key()'+ char(10) +')' + char(10) +'TABLESPACE '+ @tablespace +';' 
      + char(10) +'GRANT SELECT,INSERT,UPDATE,DELETE ON '+ @schema+ '.'+ @pre +'_'+b.name+ ' TO '+ @role1 +';'
    --  + char(10) +'GRANT SELECT ON '+@schema+'.'+ @pre +'_' +b.name+ ' TO '+ @role2 +';'
    else ',' end  
    
    AS SQL_TEXT
    
    from  syscolumns a,sysobjects b,systypes c,
    (select max(a.colid) max_id
    from syscolumns a,sysobjects b
    where b.type='U'
    and a.id=b.id 
    and upper(b.name)=upper(@tablename)) tmp 
    where a.id=b.id 
    and a.usertype=c.usertype 
    and upper(b.name)=upper(@tablename)
    order by a.colid
	
    FETCH next table_nm_cursor INTO @pre,@schema,@tablespace,@role1,@role2,@tablename
end
close table_nm_cursor
deallocate cursor table_nm_cursor