Schemas are a construct that it seems are either never used or extensively used in different organizations. In some development shops, everyone always uses the dbo schema and that's it. In other places, schemas are used for different purposes, such as staging tables with an ETL schema, auditing with an Auditing schema, and more. These allow the same table structure that might exist in other schemas, and help organize objects that are related, but separate.

I've even worked in some environments that use schemas like branches, with a schema for each developer. The developers work in their schema, such as the "Steve" schema. When their code works, someone moves their object to the dbo (or other) schema, where it gets "merged" into the main application schema. This is often a way of avoiding conflicts in a shared database. I don't recommend this, but I know that changing process is hard.

This article looks at the way in which you might try to rename all the objects in a schema to a new schema name. A quick hint, there is no rename syntax, but we'll show how you can move a class of objects to a new schema.

There Is No RENAME

I wish there were an ALTER SCHEMA ... RENAME syntax, but there isn't. Once a schema is set, we can't alter the meta data. I'm not sure why, as this is really a meta data item in a DMV somewhere. We ought to be able to do this.

I get the reasons not to allow this, as all your code will use the old name. Existing compiled plans should work, as I would guess these are bound to the object id in the plan, but any recompilation might fail because trying to find the old schema name would be an issue.

We can move objects between schemas, which is what I'll do.

Moving All Objects to a New Schema

The overall process that we'll use to "rename" a schema is to create the new schema name we want, and then move all the objects to the new schema. There isn't a bulk way of doing this, but we can walk through the process and use some dynamic execution to implement this process for all objects.

I've got a series of objects in the "SallyDev" schema that exist now in my current database. These were created by a developer, but I want to move these to a new application schema. I could move these to dbo, but I want to demonstrate a "rename", so let's move all these objects to the "College" schema. Here is the existing set of objects:

The New Schema

The first step, obviously, is to create a new schema. Let's do that with the CREATE SCHEMA syntax. 

CREATE SCHEMA College AUTHORIZATION dbo
GO

The syntax just needs a name to create the schema. However, since there is an owner for this object, and I like to ensure that any developer working doesn't create a schema that has a dependency on them, I like to use the authorization option and ensure the schemas are owned by dbo. If you forget, or need to move ownership, I've written a blog that can help you.

Once the schema exists, I can now start my process.

Moving Objects

The syntax for moving objects between schemas uses the ALTER SCHEMA syntax with the TRANSFER option. This allows me to pull objects to a new schema. In my case, I want to do this:

ALTER SCHEMA College TRANSFER [schema.object]

where the schema.object is the existing object name.

While it's easy to do this for one object, doing this for  an entire schema takes multiple commands. Since this is a rare process, I don't mind working through a list of items, and I'll actually use two structures to do this:

• a cursor of the objects to move
• a call to EXEC() with dynamic SQL being employed

These two structures will let us loop through the objects we need and move them to the new schema.

Let's start with getting the list of objects. In this case, I'll query sys.schemas for the schema name and then sys.objects for the object names. For me, I'll use a generic query to get the data. Here is where I start:

DECLARE @schema VARCHAR(100) = 'SallyDev';
SELECT 
   o.type_desc,
   s.name AS 'Schema Name',
   o.name AS 'Object Name',
   o.object_id
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE s.name = @schema;
GO 

If I needed to limit this to certain objects, I could alter the WHERE clause to limit to a type of object, a naming pattern, or something else.

In this case, I really only need the name of the object, with the old schema name. I'll now alter my column list to concatenate those items into one column, like this:

DECLARE @schema VARCHAR(100) = 'SallyDev'
      , @obj VARCHAR(200);
DECLARE RenameCursor CURSOR for 
SELECT 
   s.name + '.' + o.name AS 'Obj'
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE s.name = @schema;
OPEN RenameCursor
FETCH NEXT FROM RenameCursor INTO @obj
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @obj
    FETCH NEXT FROM RenameCursor INTO @obj
END
DEALLOCATE RenameCursor
GO
 

Now I have the object, and if I run this, I get the output of each object name as "SallyDev.Class", "SallyDev.GetClassSchedule" and so on. Now I can move on to changing the schema.

To change the schema, I'll use ALTER SCHEMA. I need to build a command, which I'll do like this:

select @cmd = "ALTER SCHEMA College TRANSFER " + @obj

This takes my variable that contains the object name and adds this to the end of the ALTER SCHEMA command. I could do this as a scalar in the cursor, but this is a little cleaner for me to read and I think easier to understand. Now I have the command by the execution. If you feel differently, let me know.

Now, let me add this to the script. I'll comment out the select and add the execution.

DECLARE @schema VARCHAR(100) = 'SallyDev'
      , @obj VARCHAR(200)
  , @cmd VARCHAR(1000);
DECLARE RenameCursor CURSOR for 
SELECT 
   s.name + '.' + o.name AS 'Obj'
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE s.name = @schema;
OPEN RenameCursor
FETCH NEXT FROM RenameCursor INTO @obj
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'ALTER SCHEMA College TRANSFER ' + @obj
EXEC(@cmd)
    FETCH NEXT FROM RenameCursor INTO @obj
END
DEALLOCATE RenameCursor
GO
 

If I run this, I'll just get the "Command completed successfully". If I check Object Explorer, I see all objects moved.

Cleanup

This isn't all I need to do. Really, I'd also need to remove the schema. I could add that as another EXEC() at the end if I desired. This would accomplish the same thing as a rename.

The other item to be aware of is that anyone running this needs to have the rights to move objects between schemas, so if you're expecting developers to move objects, ensure they have the rights to alter the schema. There is a CREATE SCHEMA permission to add the new schema. In order to move an object, you need the CONTROL permission on the object and the ALTER permission on the target schema.

Conclusion

Since there isn't a rename command, this piece is a bit of a workaround. However, since a rename isn't going to put objects in an existing schema, this will work. In fact, if you want to move to an existing schema, this will work. This can even move a class of object, or a subset of all objects if you change the WHERE clause in the cursor to suit your needs.

Hopefully you don't often have to move objects between schemas, but if you do, this will help. Just remember that all code referencing these schemas/objects need to be changed as the code will bind to the object by name at compilation.