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.