How to Migrate Between Schemas in Bulk in SQL Server

schemasql-server-2008-r2

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.

Best Answer

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.

Related Question