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.