Some time restoring database from ".bak" file might be problematic,
especially the "The backup copy provided by web hosts" "schema mapping" might create issues.
To transfer object from one schema to another following command can be used
ALTER SCHEMA schema_name TRANSFER object_name
but if we want to transfer all objects following cursor will do the job.
this might not be good approach but it worked for me.
declare @oldSchemaName varchar(100)
declare @newSchemaName varchar(100)
declare @name varchar(500)
declare @ExecQuery varchar(1000)
set @oldSchemaName='guest'
set @newSchemaName='dbo'
DECLARE cur CURSOR FOR SELECT @oldSchemaName + '.'+ name FROM sys.objects
where schema_id in(
select top 1 schema_id from sys.schemas where [name] like @oldSchemaName)
OPEN cur FETCH NEXT FROM cur into @name
WHILE @@FETCH_STATUS = 0
begin
set @ExecQuery='ALTER SCHEMA ' + @newSchemaName + 'TRANSFER ' + @name
print (@ExecQuery)
exec(@ExecQuery)
FETCH NEXT FROM cur into @name
end
CLOSE cur
DEALLOCATE cur
Subscribe to:
Post Comments (Atom)
2 comments:
Thanks for every other excellent article. The place else may just anybody get that kind of information in such an ideal way of writing? I have a presentation next week, and I'm at the search for such info.
Hello! I realize this is somewhat off-topic however I needed to ask. Does operating a well-established blog such as yours take a lot of work? I'm brand new to operating a blog however I do write in my diary on a daily basis. I'd like to start a blog so I can share my personal experience and feelings online. Please let me know if you have any suggestions or tips for brand new aspiring bloggers. Appreciate it!
Post a Comment