易企加_天马行空 CEO  2011年11月01日
悬赏100分,将sqlserver下的脚本翻译成mysql的脚本
脚本如下,就是从系统表里获取某张表的字段的一些信息,然后写到对应的自定义的元数据表Sync_MetaData,Sync_MetaItem.
create proc up_InitMeta
@tableName nvarchar(50),
@dataImporter nvarchar(1000),
@changedDataConverter nvarchar(1000)
as
begin
declare @metaDataID int,@xOrder int
select @xOrder = max(XOrder) + 1 from Sync_MetaData
if(@xOrder is null)
set @xOrder= 1
INSERT INTO [Sync_MetaData]
([TableName]
,[IgoreCase]
,[xOrder]
,[DataImporter]
,[ChangedDataConverter])
VALUES
(@tableName
,1
,@xOrder
,@dataImporter
,@changedDataConverter)
set @metaDataID = @@IDENTITY
set @xOrder = 1
INSERT INTO [Sync_MetaItem]
select @metaDataID,c.name,c.name,dbo.fc_GetTypeCode(t.name),0,c.is_nullable,null,null,null,ROW_NUMBER() OVER (ORDER BY column_id) from sys.columns c
inner join sys.systypes t on t.xusertype=c.system_type_id
where c.is_identity = 0 and c.object_id in (select object_id from sys.objects where name = @tableName)

Update k Set k.IsPrimaryKey=1 From Sync_MetaItem k Where k.MetaDataID in (select MetaDataID from Sync_MetaData where TableName=@tableName)
And Exists (Select 1 from sysindexkeys ik,syscolumns c
where ik.id=c.Id and ik.colid=c.colid and ik.id=Object_id(@tableName) and c.Name=k.ColumnName
And Exists(select 1 from sysindexes i where ik.id=i.id and ik.indid=i.indid
and i.status & 2948=2048))
end
go

create function fc_GetTypeCode
(
@typename nvarchar(50)
)
returns int
as
begin
if CHARINDEX('int',@typename) > 0
return 9
if CHARINDEX('char',@typename) > 0
return 18
if CHARINDEX('bit',@typename) > 0
return 3
if CHARINDEX('date',@typename) > 0
return 16
if CHARINDEX('decimal',@typename) > 0
return 15
if CHARINDEX('float',@typename) > 0
return 13
if CHARINDEX('text',@typename) > 0
return 18
if CHARINDEX('money',@typename) > 0
return 18
if CHARINDEX('numeric',@typename) > 0
return 14
if CHARINDEX('time',@typename) > 0
return 20
if CHARINDEX('xml',@typename) > 0
return 18

return -1
end
...全文
147 点赞 收藏 17
写回复
17 条回复

还没有回复,快来抢沙发~

发动态
发帖子
MySQL
创建于2007-09-28

2.4w+

社区成员

5.5w+

社区内容

MySQL相关内容讨论专区
社区公告
暂无公告