• 1 reply
  • Latest Post - ‏2014-02-17T18:51:08Z by Damery
304 Posts

Pinned topic What's the easiest method: Moving Tables from one schema into an other

‏2013-06-07T08:11:41Z |

Let me explain first:

Around 3 years ago I wrote an application with new tables. All these tables are created with DDL, Identity Columns are used in all tables. Primary Key Constraints are created over these identity columns. Referential integrities are defined (based on these identity columns). Additional triggers are added. All to keep data consistent. Additionally a bulk of views was created to move business logic into data base. Most of these views join the newly created tables with existing ones (mostly) within the same schema but also with tables in other schemas.

Yesterday my manager came up, these tables (and dependent objects) must be moved into a different schema. Unfortunately both schemas contain a lot of other objects, so save the library and restore it with a different name won't work.

... and unfortunately the second schema is also in the library list but AFTER the first one where my tables are located currently.

What's the easiset way to get those tables moved?

What I've done so far is to generate an SQL script containing all the creation statements for my tables and dependent objects, in the sequence I need to create the objects (Tables first with identity columns GENERATE BY DEFAULT without constraints and triggers, then copy data, change identity columns back to GENERATE ALWAYS and RESTART WITH  the next Id, then creating all constraints, views, indexes).

It works but it was a huge effort in finding all dependent objects, preparing the scripts and finally testing the result.

I only want to check if there would have been a better way.


Updated on 2013-06-08T07:46:14Z at 2013-06-08T07:46:14Z by B.Hauser
  • Damery
    71 Posts

    Re: What's the easiest method: Moving Tables from one schema into an other


    Did you ever find an easier way? I am looking into this same type of scenario with Constraints and system controlled identity columns being moved/copied from one library/schema to another and trying to .

    we are just running into the identity issue now, but still are having a hard time with maintenance scripts and constraints.

    We tried just adding to the create script but that is not working as expected.

    I have heard of people generating scripts from Navigator then altering what you need and re-running it.