This time the topic will be changing a schema for an object like a table, a view, a stored procedure in SQL Server. Actually, to be more precise, I should write that the article is about moving an object from one schema to another. A difference between changing and moving is important - a name can be changed, but a schema is a container for objects so moving or transferring an object is better, I think. Nevertheless, an object created once, can be modified later including changing its schema. Read the short description below to find out how.

Possible reason to change a schema

The way how software is developed has been changing for a long time and it still does. Agile methodologies become more and more popular over designing everything before the development starts. A fans group of Agile and Scrum evangelists grows very fast. I think that is good - I am one of them. However, it also makes some situations to happen more often than in the past. For example, I worked on a new functionality. A database design was done, tables and stored procedures were created, the case was closed. Later, a new functionality was requested and then I realized that the tables I had created earlier, would better fit a different database schema. Were you in such situation? It could happen, right?

No-brainer option

Of course, a big thinking exercise might not be needed. I can write a script that creates the tables - actually, I should have it already because I had created those tables in the first place. Then, the tables could be dropped, the old schema name in the script could be replaced with the new one, the script could be executed and voilĂ . Hmm ... is it? What about data? Yeah, right, data. It should be copied as well, the script should contain the data or, if there is much of dynamic data, then the original tables cannot be dropped till the end, because they will be used as a source during data copying. Poof. It seems like a lot of work.

Transferring option

It would be nothing to write about if there was no better way. SQL Server has ALTER SCHEMA ... TRANSFER ... option since 2005 release. The construct is easy:

ALTER SCHEMA <new schema name> TRANSFER <old schema name>.<object name>

A simple case of one independent table is simple and it simply works. Lets check a more sophisticated example.

I have four tables: account_groups, accounts, transactions and transaction_types. They all belong to ab schema and they are linked together with foreign key constraints as presented on the below diagram.

Besides them, there is IX_accounts_agr_id index on acc_agr_id column on accounts table. Additionally, I created getAccountId function and addAccount procedure.

CREATE PROCEDURE ab.addAccount (
	@id INT,
	@name VARCHAR(255),
	@groupName VARCHAR(255)
) AS
BEGIN
     INSERT INTO ab.accounts (acc_id, acc_name, acc_agr_id)
     VALUES (@id, @name, (SELECT agr_id FROM ab.account_groups WHERE agr_name = @groupName));
END;
GO;

CREATE FUNCTION ab.getAccountId (
	@name VARCHAR(255)
)
RETURNS INT
BEGIN
     DECLARE @id INT;

     SELECT @id = acc_id
     FROM ab.accounts
     WHERE acc_name = @name;

     RETURN @id;
END;
GO;

There is also the second schema - cd which I would like to transfer the objects to.

I start with tranfering the first table - account_groups from ab schema to cd schema.

ALTER SCHEMA cd TRANSFER ab.account_groups;

The command was succesful and you can see on the diagram that the table was moved to cd schema.

As you can see, the foreign key existed on accounts table referring to account_groups still exists, but now it points to the new location.

As the next step I try to transfer all other tables.

ALTER SCHEMA cd TRANSFER ab.accounts;
ALTER SCHEMA cd TRANSFER ab.transaction_types;
ALTER SCHEMA cd TRANSFER ab.transactions;

All three statements succeeded. All tables were moved to the new schema.

Once again - all foreign keys that required switching to objects in new locations were changed. The nonclustered index - IX_accounts_agr_id was moved too as it belonged to accounts table that was transferred.

One remaining question is - what happened to the stored procedure and the user-defined function?

Procedure

EXEC ab.addAccount 21, 'test test', 'silver members';

Msg 208, Level 16, State 1, Procedure addAccount, Line 27
Invalid object name 'ab.account_groups'.

The procedure became broken as the table used by the procedure was moved.

Function

SELECT ab.getAccountId('test');

Msg 208, Level 16, State 1, Line 39
Invalid object name 'ab.accounts'.

The same thing happened to the function - it is unusable because accounts table no longer exists in ab schema.

Obviously, procedures and functions can also be transferred using the same construct.

ALTER SCHEMA cd TRANSFER ab.addAccount;
ALTER SCHEMA cd TRANSFER ab.getAccountId;

Command(s) completed successfully.

If you think that it does not help, you are right.

EXEC cd.addAccount 21, 'test test', 'silver members';

Msg 208, Level 16, State 1, Procedure addAccount, Line 27
Invalid object name 'ab.account_groups'.

SELECT cd.getAccountId('test');

Msg 208, Level 16, State 1, Line 39
Invalid object name 'ab.accounts'.

I hope the reason is pretty obvious - the procedure and the function was moved but the code inside remains the same and they still expect the tables in ab schema.

Summary

The conclusion from the below exercise is the following - ALTER SCHEMA ... TRANSFER ... makes moving objects between schemas simple. There is at least one important thing to remember about - code of stored procedures and functions is not changed to reflect transferring tables.