How to migrate between schemas in bulk in SQL Server?

Question

We currently have multiple databases but would like to combine them and, instead, separate our domain contexts using schemas.

In MS SQL Server 2008 R2, how can I relocate all the contents of one schema into another in bulk?

For example, all of the tables, views, procedures, indices, etc... that we created into the dbo schema will now live in the foo schema.

EDIT: I wanted to clarify based on AaronBertrand's great comments. This is not a multi-tenancy situation. Our situation is where internal tools plugins were developed in isolation by developers who didn't merge their tables into the tool's database.

• Aaron Bertrand: This could get really tricky with FK and other dependencies. Why are you switching models? I like the multi-db model over the multi-schema model for a variety of reasons - see dba.stackexchange.com/questions/33550/… and dba.stackexchange.com/questions/16745/…
• Matthew: @AaronBertrand among the reasons are that we have relational dependencies between entities that are in separate databases (users, for example, in their own DB) and, also, that we have views that span databases and thus cannot use schemabinding.
• Aaron Bertrand: Also see stackoverflow.com/questions/11348272/#11348597 stackoverflow.com/questions/6599008/#6599008 stackoverflow.com/questions/19910088/#19910762 stackoverflow.com/questions/2254527/#2254621 stackoverflow.com/questions/11977077/#11977276 stackoverflow.com/questions/9365871/#9365929
• Aaron Bertrand: You don't need foreign keys to have relationships - these can be enforced explicitly in various other ways. And your views don't need to have SCHEMABINDING unless they're indexed. I worked with such a system for 13 years, and I can promise you that the things you're worrying about are not worth worrying about compared to the things you'll lose by combining everyone into a single database.
• Matthew: @AaronBertrand the big difference I see between my use case and that often described in your link is that these database do not separate clients or tenants in any way. Each of them is used by a combination of internal tools. Likely the reason they are separate is because the developer create the tool plugin in a vacuum then attached it without merging into the main DB. We actually will have separate DB's to deal with tenancy and different environments...

Answer 1

The basic concept is actually quite simple: you generate a script from sys.objects and sys.schemas that builds ALTER SCHEMA TRANSFER statements. So for example, you have three objects in the dbo schema, and you want to move all of them to the blat schema:

Table: dbo.foo
Table: dbo.bar
View: dbo.vFooBar

The following code:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'
 ALTER SCHEMA blat TRANSFER dbo.' + QUOTENAME(o.name) + ';'
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE s.name = N'dbo';
PRINT @sql;
-- EXEC sp_executesql @sql;

Will yield this script (but perhaps not in this order):

ALTER SCHEMA blat TRANSFER dbo.bar;
ALTER SCHEMA blat TRANSFER dbo.foo;
ALTER SCHEMA blat TRANSFER dbo.vFooBar;

(You may want to add additional filters to leave out objects in the dbo schema that you don't want to move, leave out certain object types (e.g. maybe all of your functions are utility functions and don't need to move), generate the script ordered by object type, etc.)

But there are a couple of problems with moving all of your objects to a new schema:

1. Probably a lot your code will still reference these objects as dbo.object - there is no easy way to fix this except brute force. You can probably find all of the occurrences of dbo. pretty easily, but these can also return false positives, such as EXEC dbo.sp_executesql, dbo. in comments, true references to objects that remain in the dbo. schema, etc.

2. Your dependencies will probably be completely out of whack, but I haven't thoroughly tested this. I do know that in this scenario:

   CREATE SCHEMA blat AUTHORIZATION dbo;
   GO
   CREATE TABLE dbo.foo(a INT PRIMARY KEY);
   CREATE TABLE dbo.bar(a INT FOREIGN KEY REFERENCES dbo.foo(a));
   GO
   CREATE PROCEDURE dbo.pX AS
   BEGIN
    SET NOCOUNT ON;
    SELECT a FROM dbo.bar;
   END
   GO
   CREATE VIEW dbo.vFooBar
   AS
    SELECT foo.a, bar.a AS barA
    FROM dbo.foo 
    INNER JOIN dbo.bar
    ON foo.a = bar.a;
   GO
   ALTER SCHEMA blat TRANSFER dbo.foo;
   ALTER SCHEMA blat TRANSFER dbo.bar;
   ALTER SCHEMA blat TRANSFER dbo.pX;
   ALTER SCHEMA blat TRANSFER dbo.vFooBar;
   

   The foreign keys actually migrate more smoothly than I expected (with the caveat that I'm testing on a much more recent version than you). But because the code in blat.pX still references dbo.bar, obviously executing the procedure:

   EXEC blat.pX;
   

   Is going to yield this error:

   Msg 208, Level 16, State 1, Procedure pX
   Invalid object name 'dbo.bar'.

   And dependency queries, such as:

   SELECT * FROM sys.dm_sql_referenced_entities('blat.pX', N'OBJECT');
   

   Will yield this error:

   Msg 2020, Level 16, State 1
   The dependencies reported for entity "blat.pX" might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

   And querying the view:

   SELECT a, barA FROM blat.vFooBar;
   

   Yields these errors:

   Msg 208, Level 16, State 1, Procedure vFooBar
   Invalid object name 'dbo.foo'.
   Msg 4413, Level 16, State 1
   Could not use view or function 'blat.vFoobar' because of binding errors.

   So, this could involve a lot of cleanup. And after you've fixed all the object references, you'll probably want to recompile all of the modules and refresh all of the views in the new schema. You can generate a script for that quite similar to the example above.

• Matthew: +1, it seems like I need to manually drop some FK's to do this transfer... I don't know what causes that yet
• Aaron Bertrand: @Matthew yeah, if foreign keys are giving you trouble, see this answer - you'll just need to adjust it so the re-create portion of the script references the new schema.
• Aaron Bertrand: @Matthew have you figured out what circumstance exists that prevents certain tables with foreign keys from transferring smoothly? What is the exact error message that you get? It would be interesting to know if you can bypass that by simply disabling the constraint rather than dropping it... but hard for me to test since I don't know what your exact blocker is.
• QHarr: Can something similar be used to simply copy across into another schema rather than "replace" with new schema? This would mean foo.sometable and bar.sometable would exist.