Alter Table Script Generation?

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

Home Articles Editorials Forums Scripts Blogs QotD Books SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Recent Posts

Popular Topics

Home

Home » SQL Server 7,2000 » General » Alter Table Script Generation?

13 posts, Page 1 of 2

Alter Table Script Generation?

Expand / Collapse

Author

Message

Posted Thursday, September 18, 2003 12:07 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 11, 2007 2:54 PM
Points: 10, Visits: 1

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

Post #16402

Crispin Proctor

Posted Thursday, September 18, 2003 12:13 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, June 22, 2009 10:37 AM
Points: 894, Visits: 314

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.

www.Embarcadero.com

Cheers,
Crispin

Cheers,
Crispin

I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!

Post #79966

Posted Thursday, September 18, 2003 4:16 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 11, 2007 2:54 PM
Points: 10, Visits: 1

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

Post #79967

Posted Wednesday, September 24, 2003 5:55 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, August 27, 2004 3:25 AM
Points: 12, Visits: 1

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.

Post #79968

Posted Thursday, September 25, 2003 4:59 AM

SSCertifiable

Group: Moderators
Last Login: Wednesday, July 01, 2009 2:33 PM
Points: 6,285, Visits: 809

Lockwood and Adept also offer similar products, I think price is comparable, around $200 (I think).

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
JumpstartTV - Learn Something New Today
End to End Training - SQL & .Net Training
It Depends - My Professional Blog

Post #79969

Posted Thursday, September 25, 2003 5:02 AM

SSCertifiable

Group: Moderators
Last Login: Wednesday, July 01, 2009 2:33 PM
Points: 6,285, Visits: 809

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
http://www.sqlservercentral.com/columnists/awarren/

Andy
JumpstartTV - Learn Something New Today
End to End Training - SQL & .Net Training
It Depends - My Professional Blog

Post #79970

Posted Thursday, September 25, 2003 6:34 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 11, 2007 2:54 PM
Points: 10, Visits: 1

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

Post #79971

Posted Wednesday, August 30, 2006 10:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 13, 2006 5:06 PM
Points: 2, Visits: 1

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?

Post #305114

Posted Tuesday, September 05, 2006 5:30 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:42 PM
Points: 1,260, Visits: 1,872

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

Post #306430

Venkata Reddy Gudimetla

Posted Friday, May 04, 2007 1:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 04, 2007 12:57 AM
Points: 1, Visits: 1

Ken,

I would like to know about your freeware for generating alter table scripts ...

Can I have further details about your tool?

Regards,

Venkat

Post #363229

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

Permissions

Expand / Collapse