Moving objects between schemas

Basic model changes when you’ve built your solution can be tricky, because they can require redesigning or rebuilding an entire solution. Sometimes, though, the solution can be pretty easy. Like changing an object’s schema, a task that can be done using the ALTER SCHEMA statement.

Here’s how it’s done:

ALTER SCHEMA newSchema TRANSFER oldSchema.objectName;

That’s it. This will move the object “objectName” to a new schema. There are a few things to bear in mind, though:

  • Schemas play a significant role in permissions and access control in SQL Server. Whenever you use ALTER SCHEMA, all the explicit permissions on an object will be removed as a safety measure.
  • Remember that if you move an object from one schema to another, any other object (procedure, view, function, etc) that references it will have to be updated to reflect this change – this does not happen automatically.
  • Obviously, you can’t move objects that are schema bound by other objects.

Let me hear your thoughts!

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

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