Moving Objects to a New Schema

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I haven’t had the need to move an object from one schema to another in years. Really since SQL Server 2000. I wrote about deleting a user that owns a schema recently, but that’s often a first step. The next thing I might need to do is actually move objects from that schema to a new one.

I actually ran across this command when I was looking how to move the schema to a new user. There’s actually a parameter for ALTER SCHEMA that will move objects. This is the TRANSFER argument and it works like this.

I need a new schema for the object. In this case, I’ve got a table called SallyDev.Class. I want to move this to a new schema, and I’ll choose dbo for this example. I often have had developers build in their own schema and then I’ll transfer to the dbo schema, which is almost like a merge of code from one branch (SallyDev) to another (dbo).

The format of the command is: ALTER SCHEMA <newschema> TRANSFER <object>

The new schema name is just the name, with brackets if needed. Hint, if you need brackets, rename your schema, please.

The object is the qualified name of the object, with the old schema. In this case, the command I’ll use is:

ALTER SCHEMA dbo TRANSFER SallyDev.Class

Here’s my before look:

2018-09-17 19_12_02-SQLQuery1.sql - dkrSpectre_sql2017.sandbox (DKRSPECTRE_way0u (68))_ - Microsoft

When I run the code, it works:

2018-09-17 19_13_03-SQLQuery1.sql - dkrSpectre_sql2017.sandbox (DKRSPECTRE_way0u (68))_ - Microsoft

Now my object is moved. Success!

2018-09-17 19_11_37-SQLQuery1.sql - dkrSpectre_sql2017.sandbox (DKRSPECTRE_way0u (68))_ - Microsoft

SQLNewBlogger

This is a quick view of a specific skill that can be handy. I won’t use this often, but if my team worked in this flow, or we had an issue, this not only shows how to resolve a single item move, but also helps me remember the command. I hadn’t seen this before, so a quick 10 minute blog is useful.

This also gives me ideas for other blogs, like how to automate this for a number of objects.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

One Response to Moving Objects to a New Schema

  1. Pingback: Finding Objects in a Schema #SQLNewblogger | Voice of the DBA

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.