September 16, 2008 at 8:49 am
ok, so here is what I have... I have schema aaaa and bbbb and cccc . all three have tables named Tbl1, tbl2 and tbl3 as well as 100's of sp's.
We need to add a column or a constraint or what have you to tbl1 tbl2 and tbl3 for ALL schema....
right now we are using a powershell script to script out and run the changes after scripting our the actual objects. the last update took over 3 hours for 20 tables and 30 schema.
is there anything we can do to make this an easier process? undocumented sp? something? as we add more schemas ( dddd,ffff,gggg, etc... ) its going to become unbearable to maintain.
September 16, 2008 at 8:54 am
Nope. Script your changes, apply your changes in each target environment -no silver bullet.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 16, 2008 at 8:55 am
Sounds Painfull... I'm guessing you could do using the master tables but there would be quite a complex update code to run. Are you wanting to add the same columns to all tables?
September 16, 2008 at 8:55 am
:crazy:
wonderful... was really hoping we wouldn't need to hire a "schema guy".
September 16, 2008 at 9:05 am
a.thomson2 (9/16/2008)
Sounds Painfull... I'm guessing you could do using the master tables but there would be quite a complex update code to run. Are you wanting to add the same columns to all tables?
yup.. each customer gets their own schema and if we need to update the base tables ( there are 20 of them) we need to do it across all the schemas
September 16, 2008 at 9:25 am
yeah,
Right now we basically do the following for any change.
1. Script out the base table schema with the change.
2. run a power shell script that re-scripts out copies of the changes with the new schema names.
3. the powershell script then attempts to run the alters on the target tables.
4. Rinse repeat for other objects.
did I mention that occasionally the tables we need to change have custom fields? that is a whole otehr can of worms tho. :w00t:
an to make matters worse we are trying to replicate these tables... so the script errors out on tables marked for replication. yippie!!!!! :w00t: :w00t:
September 16, 2008 at 9:40 am
Christopher Favero (9/16/2008)
did I mention that occasionally the tables we need to change have custom fields? that is a whole otehr can of worms tho.
Nice! 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 16, 2008 at 10:18 am
yeah... just looking to see if there was any light at the end of the tunnel or if the light was an oncoming train.
:sick:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply