Today I had to change the schema of a stored procedure. I couldn’t find a clicky click method to do this in SQL server management studio (which kinda worries me). So I looked on google for a method and found this. But not being an SQLguru I took a few seconds to sink in.
So the syntax is.
ALTER SCHEMA nameoftheschematotransferto TRANSFER nameandschemaoftheSP
We have an SP called username.spo1 and I want to change it to dbo.spo1 so I would change it from schema username to schema dbo. This would then mean
ALTER SCHEMA dbo TRANSFER username.spo1
The funny thing is that not even SQL prompt from Redgate recognizes the word TRANSFER as a keyword. So I guess transfering schemas is not a very common event.