Can we create a copy of schema in the same db

Question

Can we create a copy of dbo schema with dba etc.

I have one schema named odb1 and I want to create a same copy by name odb2. As of now I when I create tables with data in odb1 using SP. I am using another SP to create same object in odb2.

I want to avoid running similar sp two times, which is time consuming and use the same odb1 to duplicate odb2.

Answer 1

Below is a script to do this in T-SQL only for procedures, functions and views. The key is that the procedures should only use one-part notation. That is, only Orders, not dbo.Orders. This also applies to the name that follows CREATE XXXX.

The script creates a new schema and makes a user owner of that schema. That user has the new schema as its default schema. It is also granted permission to read definitions in the dbo schema and rights to create various objects. The script impersonates that user and gets the code for objects in the dbo schema, and just re-runs them. Since the default schema is new schema, this is there the new objects will end up. The last statement REVERT changes the user context back to yourself.

However, it does not handle tables, and overall, this is not really the correct solution. The correct solution is that you have your objects under version control, and that you retrieve the objects from version control and load them through a script in Powershell, Python, Perl or whatever you like. But this script would apply the same idea: impersonate a user that owns the schema in question and has it as its default schema.

You could also work from a source schema, but you should still use a client component for the scripting of table, which is easier to do client-side, as you can use SMO and don't have to roll your own.

CREATE USER Extra WITHOUT LOGIN WITH DEFAULT_SCHEMA = Extra  
GRANT VIEW DEFINITION ON SCHEMA::dbo TO Extra  
GRANT CREATE PROCEDURE TO Extra  
GRANT CREATE VIEW TO Extra  
GRANT CREATE FUNCTION TO Extra  
go  
CREATE SCHEMA Extra AUTHORIZATION Extra  
go  
EXECUTE AS USER = 'Extra'  
go  
DECLARE @cur CURSOR,  
        @sql nvarchar(MAX),  
        @objname sysname  
  
SET @cur = CURSOR STATIC FOR  
     SELECT m.definition, o.name  
     FROM   sys.sql_modules m  
     JOIN   sys.objects o ON m.object_id = o.object_id  
     WHERE  o.schema_id = 1  
       AND  o.type IN ('P', 'FN', 'IL', 'TF', 'V')  
  
OPEN @cur  
  
WHILE 1 = 1  
BEGIN  
   FETCH @cur INTO @sql, @objname  
   IF @@fetch_status <> 0  
      BREAK  
  
   BEGIN TRY  
      EXEC(@sql)  
   END TRY  
   BEGIN CATCH  
      PRINT @objname + ': ' + error_message()  
   END CATCH  
END  
go  
REVERT  
go  
 

  

Answer 2

Hi @Neil ,

I tried to create a copy of schema in SSMS successfully.

Please refer below steps and check whether it is helpful to you.

I have a database named TM1,one schema TMS1,one table TMS1.Table1 and one view TMS1.view1. I would like to copy schema TMS1 and create schema TMS2.

1. Right click the database
2. Select Tasks -> Generate Scripts
3. (Click next if you get the intro screen)
4. Select "Select specific database objects"
5. Pick the objects to generate scripts for (tables, stored procedures, etc...)
   
6. Click Next, then check 'save to new query window'
7. Click Finish to generate the script
8. In the open query window, you could replace all 'TMS1' with 'TMS2' in the script
9. Execute this script and schema 'TMS2' is created succesfully together with all objects in this schema.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Neil ,

You could also try with the transfer object in SMO (Transfer class) to script the entire schema to a .sql file and run that script.

Please refer more details in the marked answer in below link and check whether it is helpful.
How to copy schema and some data from SQL Server to another instance?

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Thanks much ErlandSommarskog.

I was just looking to duplicate the schema with different name. looks like this feature is not available in SQL 2016.

Answer 5

My apologise for the late response, got into other work.

Yes all the suggestions works.