Transfer Table Schema From one to dbo


 

  

declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema varchar(1000)

set @oldschema = 'IIS APPPOOL\User1'
set @newschema = 'dbo'

while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)
begin
select @table = name from sys.tables 
where object_id in(select min(object_id) from sys.tables where schema_name(schema_id) = @oldschema)

set @sql = 'alter schema [' + @newschema + '] transfer [' + @oldschema + '].' + @table

exec(@sql)
end
 
 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc3ec5b6-282c-4b6f-8161-ebb754bffc49/change-schema-sql-2016?forum=transactsql

Original Script from:
https://www.sqlservercentral.com/Forums/654301/change-old-schema-name-into-new-schema-name

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s