How to move objects from Schema to another in SQL 2005

  • Article

If you are building CLR stored procedure and you want this stored procedure to be part of a schema called xyz. When you write the name of the procedure in the SqlProcedure attribute "xyz.myproc" and deploy this procedure, the visual studio deployed it with dbo schema and the name of the procedure will be dbo.xyz.myproc.

In this case and in many other cases, you may need to move the procedure to the xyz schema. Fortunately you can do this pretty simple with only 1 SQL statement. You can use Alter Schema TSQL command for this. The syntax as following

ALTER SCHEMA schema_name TRANSFER object_name

schema_name is the name of the schema that you want the object to be transferred to (xyz in our case)

object_name is the full name of the object including its schema (dbo.xyz.myproc in our case)

Note:

Unfortunately, this TSQL statement can't deal with multi parts name (names with more than one dot) so per in mind to use names with no dot in the middle (i.e. dbo.xyz_myproc)

I think this workaround is pretty easy.