Sql-server – Convert multiple databases into schemas SQL Server

schemasql server

Due to a high amount of databases being created on my project and the rising cost per databases I had the following question:

Is there a way to convert multiple databases in SQL Server into one database with multiple schemas?

Thank you for helping out.

Best Answer

Yes, this can be done.

In the open source community, it has been done for years. In most situations, they just prefixed the tables.

I am not aware of a tool to simplify the process.

You are aware that you introduce new challenges.

  1. Permissions.
  2. Queries are more complicated and may lose some performance.

An alternative approach is to move to multi-tenant design. Add a new field to all tables. Let's call it ClientId. Add filtered indexes. Now all queries need to be filtered on ClientId. You still need to deal with any auto increment columns.