CODEWITHSUNDEEP
sql-serverdiff
AngryWebGuy
AngryWebGuy

Reputation: 133

SQL Server diff tool

Working on a team where people are prone to amending dev SQL Server tables and forgetting about it, or preparing a change for deployment and having to wait for that deployment. This leaves our dev and live tables inconsistent, causing problems when SPROCs are pushed live.

Is there a tool whereby I can enter a SPROC name and have it check all tables referenced in it in the dev and live DBs, and notify of any differences?

Upvotes: 8

Views: 15982

Answers (7)

sarh
sarh

Reputation: 6627

I've made a MssqlMerge utility that allows to compare (and merge) MSSQL database data and programming objects. It also allows to search for particular word or phrase across table definitions and programming objects.

Upvotes: 1

Rikin Patel
Rikin Patel

Reputation: 9383

SQLDBDiff is a nice and user-friendly and lite tool.

SQLDBDiff supports SQL Server 2000 to 2016 and also SQL Azure.

SQLDBDiff available with both free with limited use and full with a trial.

SQLDBDiff Screen

More Screen

Upvotes: 2

Eric Chhun
Eric Chhun

Reputation: 301

For SQL Server 2005/2008, Open DBDiff works pretty well. The great part about this is that it's free. Also note that I am writing this answer for version 0.9 which currently works for SQL 2005/2008.

It'll show you the differences between the database schema between a source database you specify and the destination database you specify. There are also buttons you can click that can update or create the table that is in question.

Upvotes: 4

Devart
Devart

Reputation: 121902

If you need a quick schema comparison tool for SQL Server, you should take a look at dbForge Schema Compare for SQL Server.

dbForge Schema Compare for SQL Server

Upvotes: 1

Gertjan
Gertjan

Reputation: 880

I would recommend SQL compare and SQL Data Compare from Redgate Software. I worked with these tools for several projects and they did a great job. Documenting changes is also a good thing to do, but some changes are to complex to write your own SQL code for (including juggling data around between tables).

The redgate tools create scripts in a matter of seconds and those scripts are almost always correct (some older versions had a hard time with table dependencies in big databases, but when playing around with the statements (in a begin transaction / rollback) I was able to quickly fix those problems).

Another strong point in the redgate suites is that you can save your comparison project. This is especially useful when you don't want to convert a certain table (or data), you can exclude them. When loading the project the next time the software will automatically ignore those tables.

One disadvantage is the cost of the software (smaller companies I worked with did not want to buy the software). SQL compare and SQL data compare together will cost you about 800 dollars, but if you look at the time you will save when releasing you will save a lot of money. There is also a trial you can play around with (30 days I believe).

Upvotes: 2

marc_s
marc_s

Reputation: 754258

I know two excellent tools for diffing SQL database structures - they don't specifically look inside stored procedures at their text, but they'll show you structural differences in your databases:

Redgate also has a SQL Dependency Tracker which visualizes object dependencies and could be quite useful here.

Marc

Upvotes: 11

MPritchard
MPritchard

Reputation: 7171

Try Microsoft Visual Studio Database Edition aka Data Dude (formerly for Database Professionals). It'll do a complete schema comparison and generate the necessary scripts to upgrade the target schema.

Of course, this shouldn't replace a proper build process ;-)

Upvotes: 1

Related Questions