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.

  • Wouldn't it simply shift your costs per database into costs per schema? Likely there will be more overhead managing a single database with multiple schemas representing multiple logical databases... I would question your motivations for doing such a thing.
    – blobbles
    Aug 27, 2014 at 7:50
  • 1
    Hypothetically, are you not just shifting 8GB split over 8 databases of 1GB into 1 database of 8GB? Are you paying for hosting per database? Aug 27, 2014 at 8:27
  • 1
    I'm using Microsoft Azure, where you pay per database. Changing to one database would lower our costs even if the size increases. Aug 27, 2014 at 9:17
  • Note that it is not enough to convert all the schemas; every line of SQL code for all applications must be corrected to properly use multi-part names. Aug 29, 2014 at 19:03

1 Answer 1


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.

  • Not sure I advocate a filtered index for every client (that's going to have an upper bound just in capacity specifications, but an earlier one in management overhead). Maybe the most demanding or largest ones. Aug 27, 2014 at 18:19
  • @AaronBertrand, your comment is valid. It was a general idea.
    – Osa E
    Aug 27, 2014 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.