Earlier I wrote about An Efficiency Tool to Compare and Synchronize SQL Server Databases and it was very much well received. From the blog post I have received quite a many question that just like data how we can also compare the schema and synchronize it. If you think about comparing the schema manually, it is almost impossible to do so. Table Schema has been just one of the concept, but if you really want the all the schema of the database (triggers, views, stored procedure and everything else) it is just impossible task. Let us learn about Schema Compare.
If you are developer or database administrator who works in the production environment than you know that there are so many different occasions when we have to compare schema of the database. Before deploying any changes to the production server, I personally like to make note of the every single schema change and document it so in case of any issue , I can always go back and refer my documentation. As discussed earlier, it is absolutely impossible to do this task without the help of third party tools. I personally use Devart Schema Compare for this task. This is an extremely easy tool.
Let us see how it works. First, I have two different databases – a) AdventureWorks2012 and b) AdventureWorks2012-V1. There are total three changes between these databases. Here is the list of the same.
- One of the tables has additional column
- One of the tables has new index
- One of the stored procedure is changed
Now let see how dbForge Schema Compare works in this scenario.
First open dbForge Schema Compare studio. Click on New Schema Comparison.
It will bring you to following screen where we have to configure the database needed to configure. I have selected AdventureWorks2012 and AdventureWorks-V1 databases.
In the next screen we can verify various options but for this demonstration we will keep it as it is.
We will not change anything in schema mapping screen as in our case it is not required, but generically if you are comparing across schema you may need this.
This is the most important screen as on this screen we select which kind of object we want to compare. You can see the options which are available to select. The screen lets you select the objects from SQL Server 2000 to SQL Server 2012.
Once you click on compare in previous screen it will bring you to this screen, which will essentially display the comparative difference between two of the databases which we had selected in earlier screen. As mentioned above there are three different changes in the database and the same has been listed over here. Two of the changes belongs to the tables and one changes belong to the procedure. Let us click each of them one by one to see what is the difference between them.
In very first option we can see that there is an additional column in another database which did not exist earlier.
In this example, we can see that AdventureWorks2012 database has an additional index.
Following example is very interesting as in this case, we have changed the definition of the stored procedure and the result pan contains the same.
dbForget Schema Compare very effectively identify the changes in schema and lists them neatly to developers. Here is one more screen. This software not only compares the schema, but also provides the options to update or drop them as per the choice. I think this is a brilliant option.
Well, I have been using schema compare for quite a while and have found it very useful. Here are few of the things which dbForge Schema Compare can do for developers and DBAs.
- Compare and synchronize SQL Server database schemas
- Compare schemas of the live database and SQL Server backup
- Generate comparison reports in Excel and HTML formats
- Eliminate mistakes in schema change propagation across environments
- Track production database changes and customizations
- Automate migration of schema changes using command line interface
I suggest that you try out dbForge Schema Compare and let me know what you think of this product.
Reference: Pinal Dave (https://blog.sqlauthority.com)
I have tried this tool, Its very helpful
In options page of this wizard you can see “My Defaults” button, this is exceptional utility for customized comparison (like, ignore case, ignore whitespace etc…)
Hi Dave. I use Microsoft visual studio 2012 premium edition. It is also posible to compare DBs schemas. Have you tried it? Do you see any advantages using dbForge? Let me know please and thanks for such a good posts!! best regards. Guiomar.
Hi there, is it only schema comparison or can we do replication of one database with another one with real time, I mean whatever changes happen in sorce the same change would be replicate in target….please let me know if you have anything related on replication of database on sql server 2012 [email removed]
Yes it is possible visual studion 2010 after comparison i am getting differential script .but i need that list of that objects information in excel sheet (table,schema,functions,view) information in excel sheet
any sql query to compare table schema of two database
Great Tool! Thanks Uncle Pinal
Very helpfull. This tool is very easy to use.
Any free tool available?
I like it, easy to use and comprehend. Thanks.
How i can Compare same table Struc. in 2 different database using TSQL
DB1- Table -> Employee
DB2- Table -> Employee
Could you please confirm, is it free software or Licensed? also share me the software down load link