</pre><pre name="code" class="sql">declare @TableName varchar(128)
declare @sqlindex varchar(max)
declare @orcindex varchar(max)
declare @mysqlindex varchar(max)
declare @orcindexStep varchar(max)
declare @ScriptS varchar(max)
declare @ScriptO varchar(max)
declare @ScriptM varchar(max)
declare @ColumnsSql Varchar(max)
declare @ColumnsOrc Varchar(max)
declare @ColumnsMy Varchar(max)
declare @ColName varchar(30)
declare @ColID TinyInt
declare @UserType smallint
declare @IndID SmallInt
declare @IndStatus Int
declare @Index_Key varchar(255)
declare @strPri_Key varchar (255)
declare @Insert_Key varchar (255)
declare @Identity varchar(1000)
declare @IdentityScript varchar(1000)
declare @IdentityName varchar(1000)
declare @IdentitySeed varchar(1000)
declare @IdentityIncr varchar(1000)
declare @Script varchar(max)
declare @owner varchar(20)
declare @num int, @sql nvarchar(1000)
declare @comment varchar(max)
declare @strcomment varchar(max)
declare @strclobfield varchar(max)
declare @clobfield varchar(max)
declare @indesname varchar(100)
set @TableName ='表名'
set @owner = '所属'
set @comment = ''
set @sqlindex = ' '
set @mysqlindex =''
set @orcindex = ' '
set @strPri_Key = ' '
Set @Script = ''
set @Identity = ''
set @IdentityScript = ''
set@Insert_Key = ''
set @clobfield =''
Set @ScriptS = 'Create Table '+@TableName + ' (' + char(13)+char(10)
Set @ScriptO = 'Create Table '+@TableName + ' (' + char(13)+char(10)
Set @ScriptM = 'Create Table '+@TableName + ' (' + char(13)+char(10)
Declare Cur_Column Cursor For
Select sc.Name
+ ' '+ Case xusertype
When 167 Then type_name(sc.xusertype)+'('+Cast(sc.Length As Varchar(10))+')'
When 175 Then type_name(sc.xusertype)+'('+Cast(sc.Length As Varchar(10))+')'
When 231 Then type_name(sc.xusertype)+'('+Cast(sc.Length/2 As Varchar(10))+')'
When 106 Then type_name(sc.xusertype)+'('+convert(varchar(1),sc.xprec)+','+convert(varchar(1),sc.xscale)+')'
When 108 Then type_name(sc.xusertype)+'('+convert(varchar(1),sc.xprec)+','+convert(varchar(1),sc.xscale)+')'
Else type_name(sc.xusertype) End
--自增列
+ ' ' + case when COLUMNPROPERTY(sc.id, sc.name, 'IsIdentity') = 1 then 'IDENTITY(' + Cast(ident_seed(so.name)As Varchar(10))+','+Cast(ident_incr(so.name)As Varchar(10))+')' else ' ' end
+ ' ' + Case when isnullable = 0 then 'Not Null' else '' end
-- 默认值
+ ' ' + (Case when sc.cdefault <> 0 then 'default' + sm.text else ' ' end )
+ ',',
'""'+ UPPER(sc.Name) + '""'
+ ' '+
(case when type_name(sc.xusertype) in('smallint','int') then 'INTEGER'
when type_name(sc.xusertype)='bigint' then 'NUMBER'
when type_name(sc.xusertype)='real' then 'FLOAT'
when type_name(sc.xusertype) ='varchar' and Length = -1 then 'clob'
when type_name(sc.xusertype) ='varchar' and Length > 0 then 'varchar2('+ convert(varchar(10), case when FLOOR(Length * 1.5) > 4000 then 4000 when Length = -1 then 4000 else FLOOR(Length * 1.5) end ) + ')'
when type_name(sc.xusertype) ='nvarchar' and Length = -1 then 'clob'
when type_name(sc.xusertype) ='nvarchar' and Length > 0 then 'nvarchar2('+convert(varchar(10),case when Length / 2 > 2000 then 2000 when Length = -1 then 2000 else Length / 2 end )+')'
when type_name(sc.xusertype) in('datetime','smalldatetime') then 'date'
when type_name(sc.xusertype)='decimal' then 'number'+'('+convert(varchar(1),sc.xprec)+','+convert(varchar(1),sc.xscale)+')'
when type_name(sc.xusertype)='numeric' then 'number'+'('+ case convert(varchar(1),sc.xprec) when '*' then '18' else convert(varchar(1),sc.xprec) end +','+convert(varchar(1),sc.xscale)+')'
when type_name(sc.xusertype)='char' then 'nvarchar2(' + convert(varchar(10),case when FLOOR(Length * 1.5) > 2000 then 2000 else FLOOR(Length * 1.5) end ) + ')'
when type_name(sc.xusertype)='nchar' then 'nvarchar2('+convert(varchar(10),sc.Length/2)+')'
when type_name(sc.xusertype)='text' then 'CLOB'
when type_name(sc.xusertype)='ntext' then 'NCLOB'
when type_name(sc.xusertype)in ('image','binary','sql_variant','varbinary') then 'BLOB'
when type_name(sc.xusertype)='money' then ' NUMBER(19,4)'
when type_name(sc.xusertype)='smallmoney' then ' NUMBER(10,4)'
when type_name(sc.xusertype)='tinyint' then ' NUMBER(3)'
when type_name(sc.xusertype)='bit' then ' NUMBER(1)'
when type_name(sc.xusertype)='xml' then ' clob'
when type_name(sc.xusertype)='sysname' then ' NVARCHAR2(128)'
when type_name(sc.xusertype)='uniqueidentifier' then ' CHAR (38)'
Else type_name(sc.xusertype) End)
-- 默认值
+ ' ' + (Case when sc.cdefault <> 0 then
'default' + case sm.text when '(getdate())' then ' sysdate ' when '(newid())' then ' sys_guid() ' else sm.text end else ' ' end )
+ ' ' + Case when sc.isnullable = 0 then 'Not Null' else '' end
+ ',',
case when COLUMNPROPERTY(sc.id, sc.name, 'IsIdentity') = 1 then sc.Name else '' end,
Cast(ident_seed(so.name)As Varchar(10)),
Cast(ident_incr(so.name)As Varchar(10)),
case when isnull(ep.[value],'') <>'' then
'comment on column '+@TableName+'.""' + upper(sc.Name) + '"" is ''' + CAST(isnull(ep.[value],'') AS VARCHAR(5000)) +''';'
else '' end,
case
when type_name(sc.xusertype) ='varchar' and Length = -1 then sc.Name + ' clob'
when type_name(sc.xusertype) ='nvarchar' and Length = -1 then sc.Name + ' clob'
when type_name(sc.xusertype)='text' then sc.Name + ' CLOB'
when type_name(sc.xusertype)='ntext' then sc.Name + ' NCLOB'
when type_name(sc.xusertype)in ('image','binary','sql_variant','varbinary') then sc.Name + ' BLOB'
when type_name(sc.xusertype)='xml' then sc.Name + ' clob'
Else '' End as clobfield,
--mysql 字段
sc.Name
+ ' '+
(case
when type_name(sc.xusertype) ='nvarchar' then 'varchar('+Cast(sc.Length/2 As Varchar(10))+')'
when type_name(sc.xusertype) ='varchar' then 'varchar('+Cast(sc.Length As Varchar(10))+')'
when type_name(sc.xusertype) ='char' then 'char('+Cast(sc.Length/2 As Varchar(10))+')'
when type_name(sc.xusertype) ='decimal' then 'decimal(' + case convert(varchar(1),sc.xprec) when '*' then '20' else convert(varchar(1),sc.xprec) end + ',' + convert(varchar(1),sc.xscale)+')'
when type_name(sc.xusertype) ='numeric' then 'numeric(' + case convert(varchar(1),sc.xprec) when '*' then '20' else convert(varchar(1),sc.xprec) end + ',' + convert(varchar(1),sc.xscale)+')'
when type_name(sc.xusertype) ='bit' then +' tinyint(1) '
when type_name(sc.xusertype) ='float' then +' double '
when type_name(sc.xusertype) ='ntext' then +' text '
when type_name(sc.xusertype) ='UNIQUEIDENTIFIER' then +' BINARY(16) '
when type_name(sc.xusertype) ='SYSNAME' then +' CHAR(256) '
when type_name(sc.xusertype) ='SMALLDATETIME' then +' DATETIME '
when type_name(sc.xusertype) ='SMALL MONEY' then +' DECIMAL(10,4) '
when type_name(sc.xusertype) ='MONEY' then +' DECIMAL(19,4) '
Else type_name(sc.xusertype) End)
-- 默认值
+ ' ' + (Case when sc.cdefault <> 0 then
' default ' +
case sm.text
when '(getdate())' then ' sysdate() '
when '(newid())' then ' UUID() '
else replace(replace(sm.text,'(',''),')','') end
else ' ' end
)
+ ' ' + Case when sc.isnullable = 0 then 'Not Null' else '' end
+ ','
--
+ ' ' + case when COLUMNPROPERTY(sc.id, sc.name, 'IsIdentity') = 1 then 'AUTO_INCREMENT' else ' ' end
from SysColumns sc
left Join sysobjects so
on so.id = sc.id
left join syscomments sm
on sc.cdefault = sm.id
left join
sys.extended_properties ep
on
sc.id=ep.major_id and sc.colid=ep.minor_id
where sc.id = Object_ID(@owner + '.' + @TableName) and sc.number = 0
order by sc.colid
Open Cur_Column;
Fetch Next From Cur_Column Into @ColumnsSql,@ColumnsOrc,@IdentityName,@IdentitySeed,@IdentityIncr,@strcomment,@strclobfield,@ColumnsMy;
While @@Fetch_Status = 0
Begin
Set @ScriptS = @ScriptS + @ColumnsSql + char(13) + char(10);
Set @ScriptO = @ScriptO + @ColumnsOrc + char(13) + char(10);
Set @ScriptM = @ScriptM + @ColumnsMy + char(13) + char(10);
set @comment = @comment + @strcomment + char(13) + char(10);
If @strclobfield <> ''
begin
set @clobfield = @clobfield + @strclobfield + char(13) + char(10);
end;
If @IdentityName <> ''
BEGIN
set @IdentityScript = @IdentityScript +
'declare V_ID int;' + char(13) + char(10) +
'Begin '+ char(13) + char(10) +
'execute immediate ''create sequence SEQ_' + @TableName + char(13) + char(10) +
' minvalue 1' + char(13) + char(10) +
' maxvalue 99999999' + char(13) + char(10) +
' start with ' + @IdentitySeed + char(13) + char(10) +
' increment by ' + @IdentityIncr + char(13) + char(10) +
' nocache' + char(13) + char(10) +
' order'';' + char(13) + char(10) +
'execute immediate ''CREATE OR REPLACE TRIGGER BI_'+ @TableName + char(13) + char(10) +
' BEFORE INSERT ON '+ @TableName + ' FOR EACH ROW ' + char(13) + char(10) +
'BEGIN' + char(13) + char(10) +
' SELECT SEQ_' + @TableName + '.nextval INTO :new.'+@IdentityName+' FROM dual;'+ char(13) + char(10) +
'END;'';'+ char(13) + char(10)
set @sql = 'select @a= max('+@IdentityName+') from ' + @TableName
exec sp_executesql @sql,N'@a int output',@num output
set @Identity = 'SEQ_' + @TableName
if @num is not null
begin
set @IdentityScript = @IdentityScript + 'execute immediate ''alter sequence ' + @Identity + ' increment by ' + cast( @num as varchar) + ''';' + char(13) + char(10)
set @IdentityScript = @IdentityScript + 'execute immediate ''SELECT SEQ_' + @TableName + '.nextval from dual'' into V_ID;' + char(13) + char(10)
set @IdentityScript = @IdentityScript + 'execute immediate ''alter sequence ' + @Identity + ' increment by ' + @IdentityIncr + '''; '
end
set @IdentityScript = @IdentityScript + char(13) + char(10)+ 'end;'
END
Fetch Next From Cur_Column Into @ColumnsSql,@ColumnsOrc,@IdentityName,@IdentitySeed,@IdentityIncr,@strcomment,@strclobfield,@ColumnsMy;
End;
Close Cur_Column
Deallocate Cur_Column
SET @ScriptS = Left(Rtrim(@ScriptS), Len(@ScriptS)- 3)
Set @ScriptO = Left(Rtrim(@ScriptO), Len(@ScriptO)- 3)
Set @ScriptM = Left(Rtrim(@ScriptM), Len(@ScriptM)- 3)
-- 索引
Declare Cursor_Index INSENSITIVE CURSOR
for Select name,IndID,status from sysindexes where object_name(id)=@TableName
and IndID > 0 and IndID<>255 and status & 64=0 order by IndID
Open Cursor_Index
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
while (@@FETCH_STATUS <> -1)
begin
if @@FETCH_STATUS <> -2
begin
declare @i TinyInt
declare @thiskey varchar(50)
declare @IndDesc varchar(68)
Select @i = 1
while (@i <= 16)
begin
select @thiskey = index_col(@owner + '.' + @TableName, @IndID, @i)
if @thiskey is null
break
if @i = 1
select @Index_Key = '""'+UPPER(index_col(@owner + '.' + @TableName, @IndID, @i))+'""'
else
select @Index_Key = @Index_Key + ', ' + '""'+ UPPER(index_col(@owner + '.' + @TableName, @IndID, @i))+'""'
select @i = @i + 1
end
if (@IndStatus & 0x02) > 0
Select @Script = 'Create unique '
else
Select @Script = 'Create '
if @IndID = 1
select @Script = @Script + ' clustered '
if (@IndStatus & 0x800) > 0
begin
select @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')'
Select @Insert_Key = ' '
end
else
Select @Insert_Key = 'Insert'
if @IndID > 1
select @Script = @Script + ' nonclustered '
Select @indesname = case when len(@TableName + '_' + @ColName) > 30 then left(left(@TableName,1) + right(@TableName,1) + '_' + @ColName,30) else @TableName + '_' + @ColName end
Select @Script = @Script + ' index ' + @indesname + ' ON '+ @TableName
+ '(' + @Index_Key + ')'
Select @IndDesc = ''
if @IndStatus & 0x01 = 0x01
Select @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ','
if @IndStatus & 0x40 = 0x40
Select @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ','
if @IndDesc <> ''
begin
Select @IndDesc = SubString( @IndDesc, 1, DataLength(@IndDesc) - 1 )
end
end
if (@Insert_Key='Insert')
begin
Set @orcindexStep = 'execute immediate ''' + replace(replace(@Script,'nonclustered',''),'clustered','') +''';'+ char(13)+char(10)
Set @orcindex = @orcindex + @orcindexStep
Set @sqlindex = @sqlindex + @Script + char(13)+char(10)
Set @mysqlindex = @mysqlindex + replace(replace(@Script,'nonclustered',''),'""','') + ';' + char(13)+char(10)
end
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
end
Close Cursor_Index
Deallocate Cursor_Index
if @orcindex != ''
begin
set @orcindex = 'begin' + char(13)+char(10) + @orcindex + 'end; '+ char(13)+char(10)
end
select @ScriptS +char(13) + char(10)+ replace(@strPri_Key,'""','') + char(13) + char(10)+ ')' as SqlScript
,@sqlindex as SqlIndex
,@ScriptO + case when @strPri_Key ='' then char(13) + char(10)+ ');' else ','+char(13) + char(10)+ @strPri_Key + char(13) + char(10)+ ');' end + @comment as OracleScript
,@orcindex as OrcIndex
,@IdentityScript as IdentityScript
,@Identity as SEQ
,@clobfield as clob
,@ScriptM +char(13) + char(10)+ replace(@strPri_Key,'""','') + char(13) + char(10)+ ')' as MySqlScript
,@mysqlindex as MySqlIndex