Change the database table schema in bulk (execute directly after generating SQL!) )
Usemy_test;--Current databaseDeclare @alltable varchar( +), @SchemaOld varchar( +), @SchemaNew varchar( +), @NewSql VARCHAR(Max), @Index INT;SET @SchemaOld='';--The original schema nameSET @SchemaNew='';--New schema nameSET @NewSql="';SET @Index=1;SELECT @alltable=IsNull(@alltable+',',"')+[name] fromSysObjectsWhereXType='U' ORDER byName;Declare @s varchar( +),@ns varchar( +);Set @s=@alltable;Set @s=@s+','; while(charindex(',',@s)<>0)begin Select @ns=substring(@s,1,charindex(',',@s)-1); --Perform Actions --SET @[email protected]+ ' alter SCHEMA ' [email protected]+ ' transfer ' [email protected]+ '. ' [Email protected]+ '; ' PRINT '/*'+CAST(@Index as varchar)+'*/Alter SCHEMA'+@SchemaNew+'Transfer'+@SchemaOld+'.'+@ns+';' ; SET @Index=@Index+1; --End ActionSet @s=Stuff(@s,1,charindex(',',@s),"');EndPRINT @NewSql;
Change the database table schema in bulk (execute directly after generating SQL!) )