SQLServer表结构转换成Oracle表结构

/******************** 
* function:sqlserver 表结构转换成Oralce 表结构，不支持索引以及自动增长
**********************/
--取消影响行数
set nocount on;

--创建表名游标
declare table_cursor cursor for
select CONVERT(varchar(300),x.name) name,CONVERT(varchar(500),y.value) value
  from sys.tables x
  left join (select major_id,value from sys.extended_properties where minor_id=0) y
    on x.object_id=y.major_id 
 where x.name='T_STATISTICS_WaterOutputMonth'
 order by x.name;

--声明变量
declare 
  @sql varchar(max)='',@primary varchar(300),@tableName varchar(300),--表名称
  @tabledes varchar(500);  --表名称描述
 
--创建表结构临时表
create table #table(colname varchar(300),isprimary int,typename varchar(50),intlength int,decimallength int,nullflag int,defaultval varchar(50),commonts varchar(500) 
)
 
--打开游标
open table_cursor;
fetch next from table_cursor into @tableName,@tabledes;
while @@FETCH_STATUS = 0
	begin 
	    truncate table #table; 
		insert into #table (colname,isprimary,typename,intlength,decimallength,nullflag,defaultval,commonts)
		SELECT CONVERT(varchar(300),a.name) [字段名],(case when (SELECT count(*)
							 FROM sysobjects
							WHERE (name in (SELECT name
											  FROM sysindexes
											 WHERE (id = a.id)
											   AND (indid in (SELECT indid
																FROM sysindexkeys
															   WHERE (id = a.id)
																 AND (colid in (SELECT colid 
																				  FROM syscolumns  
																				 WHERE (id = a.id)
																				   AND (name = a.name)))))))
							  AND (xtype = 'PK')) > 0 then  1
			   end) [主键],b.name [类型],COLUMNPROPERTY(a.id,a.name,'PRECISION') as [长度],isnull(COLUMNPROPERTY(a.id,'Scale'),0) as [小数位数],(case when a.isnullable = 1 then 1 else 0 end) [允许空],e.text [默认值],g.[value]) AS [说明]
		  FROM syscolumns a
		  left join systypes b
			on a.xtype = b.xusertype
		 inner join sysobjects d
			on a.id = d.id
		   and d.xtype = 'U'
		   and d.name <> 'dtproperties'
		  left join syscomments e
			on a.cdefault = e.id
		  left join sys.extended_properties g
			on a.id = g.major_id
		   AND a.colid = g.minor_id
		  left join sys.extended_properties f
			on d.id = f.class
		   and f.minor_id = 0
		 where b.name is not null
		 and d.name=@tableName
		 order by a.id,a.colorder

		--初始化变量
		set @sql='';

		--构建表结构
		select @sql+='
    '+case colname when 'Comment' then 'Comments' else colname end +' '
			 +case typename 
				when 'varchar' then 'varchar2('+CONVERT(varchar(10),intlength)+') '
				when 'int' then 'number(4) '
				when 'decimal' then 'number('+CONVERT(varchar(10),intlength)+ ( case when decimallength>0 then ','+ CONVERT(varchar(10),decimallength) else '' end)+') '
				when 'datetime' then 'date '
				else typename
			 end 
			 + case when defaultval is not null and len(defaultval)>0 then 'default '+
			     (case when charindex('getdate',defaultval)>0 then 'sysdate '
				       when charindex('newid',defaultval)>0 then 'sys_guid() '  
				       else (case when typename='int' or typename='decimal' then REPLACE(REPLACE(defaultval,'(',''),')','') else defaultval end )
				  end) 
			 else '' end 
			 + case when nullflag=0 then ' not null,' else ',' end
		  from #table;

		  select * from #table
		
		if @sql is not null and len(@sql)>0
			begin
				set @sql=left(@sql,len(@sql)-1);
				
				--创建表结构
				set @sql='create table '+ @tableName+'('+@sql+
				'
);
comment on table '+@tableName+' is '''+@tabledes+''';
'               
                --添加备注
				select @sql+= case when commonts is not null and len(commonts)>0 then 'comment on column '+@tableName+'.'+colname+' is '''+commonts+''';
' else '' end
				  from #table; 

				--添加主键索引 
				if exists(select 1 from #table where isprimary=1 ) 
					begin
						set @primary=''
						select @primary+= colname+',' 
				          from #table
						 where isprimary=1
						set @primary=left(@primary,len(@primary)-1);
						set @sql+='alter table '+@tableName+' add constraint PK_'+@tableName+' primary key ('+@primary+');'
					end

			end
		print @sql; 
		fetch next from table_cursor into @tableName,@tabledes;
	end
close table_cursor;
deallocate table_cursor;

drop table #table; 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!