Sql-server – Convert multiple databases into schemas SQL Server

None of performance, stability, optimization are true. Does anyone have a solid argument or reference article why these would be true?

Resources are not allocated to a database: the SQL Server Instance balances resources so it makes no difference

You lose:

• data integrity
• restore integrity (data in DB7 will be later then DB1)

You gain complexity:

• security (users, roles etc) have to be in all databases
• you'll have some data that doesn't fit into 1 database nicely

Options:

• splitting a database onto separate disks can be done with filegroups
• use schemas to logically separate data (based on other 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.