SQLServer数据迁移到Mysql表和字段comment丢失批量添加方法
第一步是用navicat将数据库迁移到MySQL过程自己百度吧。。
第二步 迁移完成之后 SQL Server 源库中执行,将所有表注解生成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
第三步 批量添加字段注解
SELECT
sql语句='select CONCAT(''alter table ' + d.name + ' modify column `' + a.name + '` '', column_type , '' comment '''''+ convert(varchar(20), isnull(g.[value],''''),120) +''''';'') aa from information_schema.columns where table_name=''' + d.name + ''' 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
大功告成 ,如有疑问可加qq 38377563询问