sp_create_table_sql2MySql 将sqlserver表结构平迁mysql

use master
go
if OBJECT_ID('sp_create_table_sql2MySql','P') is not null
drop proc sp_create_table_sql2MySql
go
create proc sp_create_table_sql2MySql ( @tablename varchar(255) ) 
as 
begin
	 -- exec sp_create_table_sql2MySql 'Ad_AdGroup'
	 -- 0. 弘恩   将sqlserver表结构平迁mysql  
	 -- 1. sqlServer move to mysql struction  only table columns and pk
	declare @sql_create varchar(max) = '';
	declare @sql_column varchar(max);
	declare @sql_primary varchar(max);
	
	with cte as 
	(
		select  '`'+ c.name +'`' +' '+
				case TYPE_NAME(c.system_type_id)  when  'bit' then ' int ' when 'money' then ' float ' else  TYPE_NAME(c.system_type_id) end  +' '+
				case when  TYPE_NAME( c.system_type_id) in  ('char','varchar','decimal','nvarchar')  then ' ( '  else ''  end +
				case when  TYPE_NAME( c.system_type_id) in  ('char','varchar','nvarchar'  )  then cast(max_length as varchar)  else ''  end+
				case when  TYPE_NAME( c.system_type_id) in  ('decimal'  )  then cast(c.precision as varchar)+','+cast(c.scale AS varchar) else ''  end+  
				case when  TYPE_NAME( c.system_type_id) in  ('char','varchar','decimal','nvarchar')  then ' ) ' else ''  end +
				case when c.is_nullable = 1 then ' null ' else ' not null ' end +
				case when c.is_identity = 0 then ' ' else '  ' end  sqlstr ,  
				
				column_id
		 from sys.objects as o
		 join sys.columns as c on o.object_id = c.object_id 
		 where o.name = @tablename and o.type = 'U'
	 )
	select @sql_column = stuff(
	(select  ',' + sqlstr  + CHAR(10)
	from cte 
	order by column_id asc 
	for xml path('') ),1,1,'')
	 ;
	
	select @sql_primary = stuff(( 
	 select ',' + c.name
	 from sys.index_columns as i 
	 join sys.indexes as ix on i.object_id = ix.object_id  and i.index_id = ix.index_id
	 join sys.columns as c on i.object_id = c.object_id  and i.column_id = c.column_id
	 where OBJECT_NAME(i.object_id) = @tablename
	 and ix.is_primary_key = 1
	  order by i.key_ordinal
	 for xml path('')
	 ),1,1,'')

	 
	 set @sql_create = ' create table if not exists `' + @tablename + '`( ' 
	 +   @sql_column  
	 + case when len(@sql_primary) >= 1 then  (', primary key ( ' + @sql_primary + ')') else '' end 
	 + ' ) '
	 
	 
	print ' -- @sql_create -- '
	set @sql_create = replace(@sql_create,'nvarchar  (',' varchar(') + ';'
	set @sql_create = replace(@sql_create,'varchar( -1 )','varchar( 4000 )') 
	set @sql_create = replace(@sql_create,' varchar  ( -1 )',' varchar(4000) ') 
	set @sql_create = replace(@sql_create,' varbinary ',' VARBINARY(400) ')
	set @sql_create = replace(@sql_create,' smalldatetime ',' datetime ')
	set @sql_create = replace(@sql_create,'uniqueidentifier ','varchar(32) ')
	 
	
	print @sql_create
end 
 GO  
 EXEC sp_MS_marksystemobject 'sp_create_table_sql2MySql' 
 GO  
LOAD DATA LOCAL INFILE 'D:/migration/h/a_account.txt' INTO TABLE a_account                                                                                                              
CHARACTER SET gbk FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\n';  
exec xp_cmdshell 'bcp db.[dbo].[T] out D:\migration\w\AdvertConfigure.txt -T -c';