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:

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.

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?

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.

Popular tags

E-privacy law

Dear User,

European Union obliged us (EU websites owners) to provide you some information.
Important is that we have never gathered or processed your personal information and we still do not.
But as majority of modern websites we use cookies and we would like to tell you a little more about them.

What are cookies

Cookies are files stored on your disk inside your computer by an internet browser when you visit some websites. They contain randomly generated unique identifiers, anonymous information about your activity on the website and preferences.

Cookies usually do not contain information that allow to track you down. This data is anonymous.

Cookies may be of varios types:

sessional - they live as long as you are on the website

persistent - they are on your disk for a specific amount of time e.g. 30 days

Purpose

We use cookies on our websites to:

enhance our service by gathering statistical data about your activity on our websites - which page you visit, for how long and what time,

make your next visit easier by remembering your choices like accepting usage of cookies

Third party cookies

As we use Google AdSense and Google Analytics on our website, our partner (Google) is using information how you interact with our website to customize and adjust advertisement's content for you personal preferences.

Your rights

You have full rights to request access to this information, to update it, to delete it or to restrict processing it. However, remember that not in all cases it might be possible to fulfil your request in regard information stored in cookies.

You can disable cookies in your browser by following steps that depend on the browser you use - Managing cookies in your browser.
Cookies can be disabled just on our websites by clicking I decline in this message window. However, keep in mind that some functionality may not work properly from technical reasons after you disable cookies for example we will not be able to present you videos from Youtube, show you social media buttons, display personalised advertisements.

Once you make a decision about cookies, you can change it whenever you want by enabling cookies in your browser or clicking Remove cookies or Reconsider cookies buttons on our websites.

EU e-Privacy Directive

Dear User,

European Union obliged us (EU websites owners) to provide you some information.
Important is that we have never gathered or processed your personal information and we still do not.
But as majority of modern websites we use cookies and we would like to tell you a little more about them.

What are cookies

Cookies are files stored on your disk inside your computer by an internet browser when you visit some websites. They contain randomly generated unique identifiers, anonymous information about your activity on the website and preferences.

Cookies usually do not contain information that allow to track you down. This data is anonymous.

Cookies may be of varios types:

sessional - they live as long as you are on the website

persistent - they are on your disk for a specific amount of time e.g. 30 days

Purpose

We use cookies on our websites to:

enhance our service by gathering statistical data about your activity on our websites - which page you visit, for how long and what time,

make your next visit easier by remembering your choices like accepting usage of cookies

Third party cookies

As we use Google AdSense and Google Analytics on our website, our partner (Google) is using information how you interact with our website to customize and adjust advertisement's content for you personal preferences.

Your rights

You have full rights to request access to this information, to update it, to delete it or to restrict processing it. However, remember that not in all cases it might be possible to fulfil your request in regard information stored in cookies.

You can disable cookies in your browser by following steps that depend on the browser you use - Managing cookies in your browser.
Cookies can be disabled just on our websites by clicking I decline in this message window. However, keep in mind that some functionality may not work properly from technical reasons after you disable cookies for example we will not be able to present you videos from Youtube, show you social media buttons, display personalised advertisements.

Once you make a decision about cookies, you can change it whenever you want by enabling cookies in your browser or clicking Remove cookies or Reconsider cookies buttons on our websites.