I manage a 280gb SQL Server database.
The structure contains a single database file and many schemas. Each customer has a schema and an associated set of tables. so :
MainDatabase : Schema1: Table 1 Table 2 Sprocs: sproc 1 Users Schema 1 user Schema2 -> Table 1 Table 2 Sprocs: sproc 1 Users Schema 2 user Schema3 -> Table 1 Table 2 Sprocs: sproc 1 Users Schema 3 user
We have around 2600 schemas within the database, over time it has gotten unwieldy and we need to break it down.
I want to move towards a horizontal growth approach, chopping the database up in 500 schema chunks. Breaking it up into 4 and continuing growth in this way.
What SQL statements could I use to achieve this? I can manually make the new databases but I need to initialise a move of this data into its new database, retaining all objects, data, stored procedures, users etc...