Duplicate objects belonging to a schema to another schema

Goal

To duplicate all the tables, views, storedprocedures, userdefinedfunctions that belongs to a schema to another schema.  Of course that there are more objects that may need to be duplicated but as a start, those are the main ones that the script does. Other objects can be added with little modification to the script.

My way of achieving it

Using powershell and scripting

The script tries to do what generate script option at the database level does but on top is trying to address the following

 

  • It tries to resolve the name of the objects inside of all the generated code to point to the objects in the right schema( new schema) – this is where the script might have an advantage over the generate script option at the database level.
    • Meaning that if the object definition was in dbo schema – SELECT * from software_definition – it will transform it in SELECT * from testuser.software_definition. As you can guess this string replacement might take a lot of time if we have a lot of objects that are scripted and the object definition is long. More than that, that string replacement might be wrong sometimes. This is why some more tests needs to be conducted.
  • It tries to decrease the need of the manual work

The output of the script looks like below

duplschema2 duplschema1

Please use it first on your virtual machines or your test instances, like adventure works sample databases or others. As I said it is not a replacement for generate script option it is just an attempt to avoid some

– clicking operations

– selecting some scripting options

– finding and replacing some strings, schema.name to newschema.name

– adding new schema name for the objects that are missing the schema prefix ( this needs to be adjusted to work as good as possible)

The code can be downloaded from here