Procedure to clone a schema in SQL Server

Question

On Azure SQL, we use schema-based multitenancy. To add a new tenant, we want to copy a schema that acts as a template for a new tenant schema. What is the easiest way to clone a schema, incl. the data?

IMPORTANT

I can't use any UI tools, so I'm looking for a procedure to execute in the database. And I also don't want to execute a pre-generated script.

I'm looking for something like this:

exec clone-schema(<source_schema>, <target_schema>)

• J.D.: Would you be open to database multi-tenancy instead? There's native ways to clone databases. It's also generally better from a performance, security, and management standpoint to segregate tenants by database.

Answer 1

There are no built-in tools that will do this for you. So you are left with a few options.

One of the restrictions that you may not be aware of is that object names (including constraint names) must be unique, so in addition to copying and renaming the schema, you would need to rename the constraints, indexes and other items.

But this is not the best approach for this type of issue. It sounds like you are trying to make it so that a new tenant can be added in a self-service manner, so you probably aren't interested in using a Visual Studio Database Project (what I would use) to handle these deployments of a new tenant.

So you are going to have to develop your own solution. I would start here https://stackoverflow.com/questions/2505728/create-table-structure-from-existing-table, using the answer by Steve (a few down from the first), that has the code for a stored procedure called "[dbo].[spCloneTableStructure]".

Then it's a matter of wrapping some code that will loop over the source schema and copy/rename the objects and then copy data from one to another as required.

• Simon Martinelli: Thanks for your answer. Yes, this looks like a start. As we are not SQL Server experts we may look for someone to write the script.
• Martin Smith: "object names (including constraint names) must be unique" - only within a schema. So there is no need to change any of these names - just need to ensure that all are created within the new schema. i.e. if schema foo has tables foo.T1, foo.T2 and constraints foo.PK1 and foo.PK2 then you can create new schema bar and bar.T1, bar.T2, bar.PK1 and bar.PK2

Answer 2

I share the following code that I found, I hope it works for you

https://github.com/JosueVSDz/CloneDatabaseSchema

• John K. N.: While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review