How to compare schemas in SQL Server 2008

Can someone guide me on开发者_开发知识库 how can I compare schemas in SQL Server 2008.

I want to do so to check if sql objects already exists and if they exists is there any change between the existing and the new one.

You can compare schemas in SQL Server 2008 and any new SQL Server with ApexSQL Diff and it has free Community edition.

It has user friendly UI, where you can easily pick your databases (or other data sources as backups, source control projects, script folders) compare them, get results with differences and to synchronize from source to destination database.

If you just want to compare objects to only see differences and create a merge script, you can check out a free tool - ApexSQL Compare.

One more tool that can do a job that you want is dbForge Schema Compare

Get a tool such as Red Gate's SQL Compare.

You'll need to pay for it, but it pays for itself through time saved.

It is available on a trial basis if you have an immediate need.

I don't recommend you do that if you can avoid it.

If you just want to compare two SQL Server databases, I recommend a low cost alternative http://cdttools.com/sql-diff-erence/. (caveat: I wrote it).

However for what you are referring to I recommend a different approach. you should already know the different version of the various schemas for your product that you have already shipped. So build a change script based on upgrading each of them completely to your latest version. That way you installer only has to detect a single version number and chooses its sql actions based on that.

If you really really really want to do SQL comparisons during an installer yoo might want to take a look at the SMO (Shared Management Objects) that are part of the SQL server feature pack. But you will most likely have to install those before you can use them.

Then you can use them to generate the scripts for each SQl object and do a comparison in your installer. However I honestly don't recommend this.

It will tell you if they exist, and allow you to generate either "alter" or "create" script versions. Alternatively you can go directly to the INFORMATION_SCHEMA database system objects that will allow you to query directly for objects existence.

In Visual Studio 2012, go to SQL (menu item) -> Schema Compare. Click on Select Source on the left, and select the dev database. Select the production database on the right.

Then click on the generate script icon (next to the Update icon, it looks more or less like a music icon). That will generate the update script. You may also untick any of the changes that you do not want to include in the script. And it is ... 'free' if you happen to own Visual Studio 2012