CODEWITHSUNDEEP
sql-server-2008sql-server-2008-r2sql-server-2008r2-express
Vikrant More
Vikrant More

Reputation: 5442

How to generate the Alter,Update & Create Script in SSMS?

Hi I am using SQL SERVER 2008 R2, I have 2 databases

1] ABC (with only structure like schemas, tables but no data in the table) 
2] ABC1 (with schemas, tables and data in the tables)

I did the comparision at Schema and Tables level, I found a table altered in #2 but not in #1 ,I want to know how can I generate the alter script to alter table in #1 ? And how can I generate the Alter,Update & Create Script which are missing in #1 ? 1st is the old DB and #2 is new .

Upvotes: 2

Views: 6972

Answers (3)

mehdi lotfi
mehdi lotfi

Reputation: 11571

You can use software for compare same DB schema. this software after compare database schema between multi DB, get you script for unifying DBs.

for example in redgate software :

enter image description here

  1. See Compare result for selected object
  2. select object that want to unify
  3. start unifying dbs

Upvotes: 0

Himanshu
Himanshu

Reputation: 32602

The ALTER To option is disabled in the Script Table as menu option! So if you want to generate a script, how do you go about doing it? Well, you can’t do it this way when you alter a table.

In order to generate the script, you first right-click on the table you want to alter and choose Design.

enter image description here

Now go ahead and add your new columns, change the field types etc. Once your changes are done, you want to right-click on any column or in the white space and you’ll see the option Generate Change Script is now available.

enter image description here

Now go ahead and save that file and you’ll have your ALTER TABLE script!

Source: How to Generate an Alter Table Script in SQL

Upvotes: 4

gangreen
gangreen

Reputation: 909

As SSMS says, you will want to review the script, but all you need to do is Right click the table in #2, choose Script Table as > DROP And Create To > File...

Then run the script on #1.

Depending on how much data you have in #2 (and the likelihood you missed some differences between the two databases), it might be easier to restore a backup of #2 and then run a script that truncates all the tables.

Upvotes: 1

Related Questions