TSQL to copy objects from one schema to another schema


  • Hi,
    I have a database containning 4 different scemas, and one of the schemas is the general one.

    How can I copy objects (i.e tables, views, etcw) from one schema to another, keeping the object in the source schema. I do not want to use transfer schema, as it does not keep

    the objects in the source schema.

    Is there any TSQL script, or operations that I can do?

    Also if I want to re-create all other schemas from my main schema (copying all objects to my other 3 schemas) - What is the best way for doing it?

    thank you,

    Saturday, December 13, 2008 8:46 PM

All replies

  • This is an odd requirement - can I ask what the business reason is for doing this?

    If it's a one-time copy, the simplest way to do it would be to just script out the objects from the old schema, and do a "Find and Replace" to change the scripts to the new schema.  For instance, if you're using SQL 2005 or 2008, you would connect to the server via SSMS, expand "databases", and expand the database of your choice.  Click on an object node (for instance, tables), and on the right hand side, select all of the tables in the "general" schema.  Right-click, and select Script Table As > Create To > New Query Analyzer Window.

    In the new window, use Edit > Find and replace to replace [oldschema] with [newschema].

    Does this help?
    Aaron Alton |
    Saturday, December 13, 2008 9:31 PM
  •  i need to develop an SP or some process that will do it automatically - doing it one time does not help me.

    these schemas serve each one - different programmers - schema for each programmer. this way, the programmers can work on thier schema and make any DML and DDL change.
    The main schema is the dev_general schema that keeps on being update with each new version. When the new version loaded, I want them to be able to get latest version to thier schema from the main schema.

    Sunday, December 14, 2008 7:01 AM
  • any advice?
    Monday, December 15, 2008 10:53 AM
  • One schema per programmer..?

    Personally, I take a backup of the live database and restore it to my local instance or local VMWare and can play with it to my hearts content then!

    We then use source control and script out any changes that require application to the live environment (after testing and QA obviously ;) and apply them in a maintenance window / scheduled job.

    Every night a full backup of the db is taken and our devs are allowed to take this and restore any time they like to their local setup.

    Monday, December 15, 2008 10:57 AM