第一步是用navicat将数据库迁移到MySQL。
第二步 批量添加表注解:在SQL Server 源库中执行一下sql语句,将所有表注解生成sql语句,将生成的sql语句在Mysql中执行,所有表注解批量修改成功
SELECT 'alter table ' + d.name + ' comment '''
+ convert(varchar(20),isnull(f.value,''),120) +''';'
FROM
sysobjects d
right join
sys.extended_properties f
on
d.id=f.major_id and f.minor_id=0
where d.name is not null
将生成的sql语句在Mysql中执行,所有表注解批量修改成功
第三步 批量添加字段注解: 在SQL Server 源库中执行一下sql语句,将所有表注解生成sql语句,将生成的sql语句在Mysql中执行,所有表注解批量修改成功
SELECT
sql语句 = 'select CONCAT(''alter table ' + d.name
+ ' modify column `' + a.name
+ '` '', column_type , '' comment '''''
+ convert(varchar(300), isnull(g.[value], ''''), 300) + ''''';'') aa
from information_schema.columns
where table_name=''' + d.name
+ ''' and column_name=''' + a.name
+ ''' and TABLE_SCHEMA = ''' + 'dreamplay' + ''' union '
FROM syscolumns a
LEFT JOIN systypes b ON a.xusertype = 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.major_id
AND f.minor_id = 0
WHERE g.value IS NOT NULL
ORDER BY a.id, a.colorder
替换为自己的数据库名称
复制查询得到的SQL语句,在MySQL库中执行语句
复制查询得到的SQL语句,在MySQL库中执行语句
在参考文章上做了一些优化,以下为参考文章: