存储过程.SQLSERVERobj2oracle

/****** Object: StoredProcedure [dbo].[obj2oracle] Script Date: 2016-04-28 11:45:37 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--存储过程.SQLSERVERobj2oracle
CREATE PROCEDURE [dbo].[obj2oracle]
@tname varchar(64) = ''
AS
set nocount on
declare @i int, @l int
if @tname = '' set @tname = '%'
create table #names(id int identity(1, 1), tname varchar(64))
insert into #names (tname)
select name from sysobjects where xtype = 'U' and name like @tname and len(name) = 4 order by name
set @l = @@ROWCOUNT
set @i = 1
while @i <= @l
begin
select @tname = tname from #names where id = @i
select 'create table ' + rtrim(@tname) + '('
union all
select case cid when 1 then ' ' else ' , ' end
+ case cname when 'SysDate' then '"SYSDATE"' else cname end + ' '
+
case xctype
when 'char' then 'varchar2'
when 'nchar' then 'varchar2'
when 'varchar' then 'varchar2'
when 'nvarchar' then 'varchar2'
when 'bigint' then 'number(20)'
when 'int' then 'number(16)'
when 'smallint' then 'number(5)'
when 'tinyint' then 'number(3)'
when 'bit' then 'number(1)'
when 'numeric' then 'number'
when 'decimal' then 'number'
when 'real' then 'float'
when 'float' then 'float'
when 'money' then 'number(18, 4)'
when 'smallmoney' then 'number(18, 4)'
when 'date' then 'date'
when 'time' then 'date'
when 'datetime' then 'date'
when 'smalldatetime' then 'date'
when 'datetime2' then 'date'
when 'datetimeoffset' then 'date'
when 'timestamp' then 'timestamp'
when 'text' then 'clob'
when 'ntext' then 'clob'
when 'image' then 'blob'
when 'binary' then 'blob'
when 'varbinary' then 'blob'
when 'xml' then 'xmltype'
when 'uniqueidentifier' then 'row(16)'
when 'sql_variant' then ''
when 'cursor' then ''
when 'table' then ''
when 'rowversion' then ''
when 'hierarchyid' then ''
when 'CLR' then ''
else ''
end
+
case xctype
when 'char' then '('+rtrim(convert(varchar,sizes))+')'
when 'varchar' then '('+rtrim(convert(varchar,case when sizes > 4000 then 4000 else sizes end))+')'
when 'nchar' then '(' + rtrim(convert(varchar ,sizes)) + ')'
when 'nvarchar' then '(' + rtrim(convert(varchar ,sizes)) + ')'
when 'numeric' then '(' + rtrim(convert(varchar,Prec)) + ',' + rtrim(Convert(varchar,Scale)) + ')'
when 'decimal' then '(' + rtrim(convert(varchar,Prec)) + ',' + rtrim(Convert(varchar,Scale)) + ')'
else ''
end
+
case isnull(defvalue, '')
when '' then ''
when '(getdate())' then ' default (sysdate)'
when '(''1899-12-30'')' then ' default (to_date(''1899-12-30'', ''yyyy-mm-dd''))'
when '(''1899-12-31'')' then ' default (to_date(''1899-12-31'', ''yyyy-mm-dd''))'
when '(''1900-01-01'')' then ' default (to_date(''1900-01-01'', ''yyyy-mm-dd''))'
when '(''9999-12-31'')' then ' default (to_date(''9999-12-31'', ''yyyy-mm-dd''))'
else ' default' + defvalue
end
+
case nulls when 0 then ' not null' else '' end
from v_col where tname = @tname
union all
select ')'
union all
select 'pctfree 5 pctused 85;'

--主键
if exists(select * from sysindexes where id = object_id(@tname) and keycnt = 1)
select 'alter table ' + tname + ' add constraint PK_' + tname
+ ' primary key (' + cname + ') using index pctfree 5;'
from v_col where tname = @tname and cid = 1
set @i = @i + 1
end

if object_id('tempdb..#names') is not null drop table #names

GO

EXEC sys.sp_addextendedproperty @name=N'Ver', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'obj2oracle'
GO