Copy Tables between Multiple Schemas

Question

Copy Tables between Multiple Schemas

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 1

FWIW, what's shown above is what some tools do.

Red Gate has SQLCompare, and it can easily script these changes and you could search/replace to all schemas.

Personally I'd build a script here, and loop over a list of schemas, altering my code each time to dynamically run this.

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 2

You know, it will be a fairly large undertaking to setup all of the scripts, but after they are all setup you shouldn't have to touch them. Seems like just digging in and writing a script then copying it over and over again may be your best bet.

Arju SSC Veteran Points: 243 More actions · Answer 3

Hi,

I do have same requirement to move a table belongs to one schema(ex: dbo) to 30 other schemas.

I have used the alter statement given in this article.. But the table properties ,default values and primary keys were not being applied to new tables.

This is more useful incase of backup tables.

Is there any way to get create same table to multiple schemas with all the table properties (identity, primary key, default column values)..??

Thanks

Karnatakapu Mallikarjuna Rao

Arju SSC Veteran Points: 243 More actions · Answer 4

Hi,

I do have same requirement to move a table belongs to one schema(ex: dbo) to 30 other schemas.

I have used the alter statement given in this article.. But the table properties ,default values and primary keys were not being applied to new tables.

This is more useful incase of backup tables.

Is there any way to get create same table to multiple schemas with all the table properties (identity, primary key, default column values)..??

Thanks

Karnatakapu Mallikarjuna Rao

Arju SSC Veteran Points: 243 More actions · Answer 5

Hi,

I do have same requirement to move a table belongs to one schema(ex: dbo) to 30 other schemas.

I have used the alter statement given in this article.. But the table properties ,default values and primary keys were not being applied to new tables.

This is more useful incase of backup tables.

Is there any way to get create same table to multiple schemas with all the table properties (identity, primary key, default column values)..??

Thanks

Karnatakapu Mallikarjuna Rao

GSquared SSC Guru Points: 260824 More actions · Answer 6

Use the Generate Scripts option in Management Studio to script all your objects, including schema names. Use search-and-replace to replace the schema name with a different schema name, run the script. Repeat as needed. Then script your insert statements the same way, and run once on each schema.

Lots of work? Sure. Dumb idea? Probably. But maintaining 30+ schemas with the same tables is a bad idea, so solutions for how to do it are all going to be problematic. Honestly, whomever is requiring you to do this probably just doesn't understand what schemas are for.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon