Compare the Schemas of two Databases

In this simple project without even writing a single line of code, now I'll show you how easy it is, compare the schemas of two databases and make them identical with a few steps.

Step 1: How to create an empty database:
 
Choose New from the File menu and 
then click Project. 
This opens the New Project dialog box. 
If you want to create a SQL Server 2000, 
click SQL Server 2000 Wizard. If you want to 
create a SQL Server 2005, click SQL Server 2005 Wizard. 
In Name, type EmptyNW and click OK. 
This opens the New Project 
Wizard database. 
Click Configure generation / distribution. 
In connection destination, 
click Browse and specify the connection to the database 
server where you want to create a blank database.
Click Finish.
A database project called SchemaDB
is created and appears in Solution Explorer. 
Click SchemaDB database project in 
Solution Explorer.
Please choose from the menu DistribuisciNomeProgetto 
Compilation.
The database project will be generated and distributed to the 
specified server.
Step 2: How to compare the schemas of two databases: 
 
Open Database Edition Schema Compare and choose the 
Data menu and then click New Schema Compare. 
This opens the dialog box Confrontoschema. Use this dialog
to specify the source and destination. In this dialog you define 
the server that hosts the source and destination, the name of each database
and the type of authentication to use to connect to each database. 
It will also open in the background window Database Edition Schema Compare 
which automatically assigns a name, such as SchemaCompare1.  
In Source database in the Server name, type the name 
of the server that hosts the Northwind database or 
any other database that you want.  
Under Select or enter a database name or typeNorthwind DB name you chose above.  
In the target database in the Server name, 
type the name of the server where 
the database resides EmptyNW. This name mustbe placed on the sameserver as the source.  
Under Select or enter a database name and type EmptyNW Click Finish.  
Now starts the comparison of the patterns. 
After the comparison, the structural differences 
between the two databases will 
be shown in the table in the Schema Compare. 
The table displays a row for each database hosted on a database.The 
database objects as you well know, are 
organized by type: tables, views, stored procedures, roles and so on. 
STEP 3: Write in the target database updates:  
Schema Compare toolbar, click Write Updates.  
You will perform the update actions listed in the Schema Compare window. 
With this timing, the pattern of the destination is changed to match 
that of the source. NB: 
You can undo an action "Updating the Data menu, click Compare schema, 
and then clicking Stop Writing to the destination.  
The comparison is not updated automatically. If you want to rerun the 
comparison to verify that the selected updates have been applied, click the 
Refresh button on the Schema Compare toolbar. 
STEP 4: Examine and run the synchronization script:  
Choose Schema Compare Data menu, choose Export and then Editor. 
You can also click 
Export Editor Schema Compare toolbar.  
Editor will open T-SQL in connected mode, synchronization with the T-SQL 
script appears. The name of this window is similar to  
Server.Northwind - SchemaUpdate_EmptyNW_1.sql. This window displays 
the T-SQL script, a file inMy Documents / Visual Studio Projects 2005/My O 
Documents / Visual Studio Projects 2008/My 
(depends where you created the project). 
As you have read and write access to this window, you can edit the script. 
If you change it, choose Save from the File menu.  
To synchronize schemas of two databases, run the script by clicking Run 
SQL Schema 
Compare toolbar or pressing F5.
NB: 
The comparison is not updated automatically. If you want to rerun the 
comparison to verify that the selected updates have been applied, 
click the Refresh button on the Schema Compare toolbar. 
At this point, you can compare data in two databases and see if they are 
completely identical! 
Have fun to all!