We’ve previously looked at the basics of using schemas in SQL Server where we created a schema and used objects inside of it. If you’re not dealing with new objects though and wanted to move something into or out of a schema how to we go about that?
Not by renaming it
For renaming most objects within SQL Server we can use the sp_rename procedure. This would work for renaming a table itself, or an index or constraint on a table, however when it comes to moving objects into another schema this won’t work.
As we said in the previous article, schemas are security features within a database rather than just a piece of metadata or part of the name. As a result we can’t just rename the object to pop it into the schema.
We need to transfer it
To move an object in to another schema we need to alter the schema and make use of the TRANSFER clause to move the object into it. We’ll look at a practical example using the sample below:
CREATE TABLE dbo.NewTable (
ID INT IDENTITY(1, 1) PRIMARY KEY,
SomeText VARCHAR(50),
AnotherNumber INT
);
GO
CREATE SCHEMA NewSchema
AUTHORIZATION dbo;
GO
With our new schema created we want to be able to move our table into that schema. We’ll issue an ALTER SCHEMA command and request the table to be transferred into it:
ALTER SCHEMA NewSchema
TRANSFER dbo.NewTable;
This will move the NewTable over to the schema. The table is now be queryable in the usual way as we haven’t applied any bespoke security to the schema:
SELECT *
FROM NewSchema.NewTable;
When issuing this command it’s also possible to specify the type of entity being changed in case there may be any ambiguity. We could have explicitly indicated an object was being renamed:
ALTER SCHEMA NewSchema
TRANSFER OBJECT::dbo.NewTable;
Renaming a schema
Similar to how moving an object into a schema doesn’t follow the regular pattern, the same is true for renaming a schema. In fact it’s completely different – you can’t.
That’s right, you can’t actually rename a schema in SQL Server.
If you want to change the name of the schema which currently has objects you’ll need to recreate the schema and then transfer the objects over to it again. There are caveats with this however, for example any permissions associated with the object being moved – or which were explicitly applied to the previous schema – will need to be recreated in the new schema. We’ll look into this more at a later time.
Wrap up
With schemas being a security mechanism and not only part of a table name it isn’t quite as simple as using the same rename commands to make these changes with a table. That said, once you know its only a slightly different command it’s just as straight forward to achieve.
Short and sweet this week. Once you know, you know, I guess.
Andy Brownsword 