September 18, 2003 at 12:07 am
Hello,
I have made extensive changes to a table via Enterprise Manager. I also have 40 other databases with the same table, which I need to alter to reflect my changes to the 1st table. What is the best way to generate an alter table script to reflect the changes, so I can simply run this new script on my 40 tables (I hope I don't have to do this by hand). Is there a tool to do this?
Thank you for reading! - Ken
September 18, 2003 at 12:13 am
Greetings.
There are tools such as Embarcadero and RedGate. Favorite is Embarcadero 🙂
They compare two DB's or selected objects and generate alter script to sync them.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
September 18, 2003 at 4:16 am
Crispin, Thanks for the reply. I had a look at those tools. No way can I afford them. I'll hold out for a cheaper solution, or I may write a tool myself if there seems to be a need for doing this. I do appreciate your reply. - Ken
September 24, 2003 at 5:55 pm
This brings me to a question that we have been kicking around here at my place of work: Enterprise Manager, like ER Studio and some of the other tools I've used, change tables utilizing the method of renaming the table, creating the new version, inserting the old data into the new table, and drop the temporarily named table. Often, we will hand create alter table/alter column scripts, with associated constraints when applicable. In our effort to avoid as many pitfalls as possible, does anyone have opinions, experience or gotchas to share on the rename/create/insert/drop methodology or alter table approach - excluding the time factor between utilizing the GUI to help script vs. handwritting, that is not the intent of the question.
September 25, 2003 at 4:59 am
Lockwood and Adept also offer similar products, I think price is comparable, around $200 (I think).
Andy
September 25, 2003 at 5:02 am
I'm definitely an advocate of using a tool (pick one) to generate the change script. The tool catches all the gotchas, so doing the change becomes a whole lot less intense. Hand coding - in my opinion - is a waste of time and error prone.
I consider a compare tool a must have, just like having a file compare utility and a source code compare. The ability to compare/sync is invaluable.
Andy
September 25, 2003 at 6:34 am
Hi Andy,
I looked at the Lockwood ($399) and Adept ($240 for Personal) products. I did like the look of the Lockwood product, but for me it is still to much money. Anyhow, a few days ago I began writing my own compare tool. It may not have all the bells and whistles of the professional products, but it will do for my needs. I expect to make it initially "freeware", so if anyone would like to have a look at it and feedback comments, please let me know!
Cheers - Ken
August 30, 2006 at 10:59 am
Ken, did you ever get that code written to essentially create the alter statements needed to recreate a copy of the table yo uwant to change?
September 5, 2006 at 5:30 pm
In either 2000 or 2005, you can have SQL generate the change script for you, then cut & paste it to run against your other tables. That's what we do. Just click on "Save Change Script" in 2000, or "Generate Change Script" in 2005.
Developers make a change to a table in the test environment and script out the change via Enterprise Manager. Then the script can be run in Production, ensuring that it's exactly the same as test area.
Perhaps it would be worth spending money on a tool if you're making tons of changes, but for us, using what's built into SQL is good enough
May 4, 2007 at 1:14 am
Ken,
I would like to know about your freeware for generating alter table scripts ...
Can I have further details about your tool?
Venkat
June 1, 2008 at 11:54 am
Ken, I have spent all day looking for a freeware tool to do this 🙂 If yours is available and works from the command line then please could you let me have the url.
Thanks
June 1, 2008 at 6:37 pm
cwedgwood50, What functionality are you looking for that the built in SQL script generator function doesn't do ??
June 2, 2008 at 6:17 am
Hi homebrew01
I'm trying to find a commandline utility that will provide change scripts to bring one copy of the database in sync with a second copy of the database. I want to integrate this into an automated release control/change management process which can continue after the database has been deployed live. There should be no manual process of running SQL Managememnt studio required.
I have several tools that can just export the sql to re-create the database from scratch, but I specifically need it to issue a script that can just be applied as a patch and stored in source control. It should be possible to generate this patch by just altering the model and running the tool.
I have found commercial tools that can do it, but I am looking for freeware or open source solutions.
Thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply