I'm working on a saas application which has an sql server database. I have tables, functions, and stored procedures in a particular schema (i.e. customer1.table1, customer1.spGetCustomers, etc.). I would like a way of copying the entire schema tables, functions, stored procedures, indexes, keys, etc. to a new empty schema for each new customer. I was hoping there was an fast easy way to do this so that I can add new schemas for every new customer and keep everything completely separate. I don't want a new database for each customer because of the cost and extra maintenance.
Please help.
Use SQLCMD variables in create scripts.
You will write schema independent create object scripts, something like:
CREATE TABLE [$(schema)].Table1 ...
CREATE PROCEDURE [$(schema)].Proc1...
Then you will execute it as:
sqlcmd -v schema ="Customer1" -i c:\script.sql