SQL Database Comparison

Question

I have two databases: DBTarget and DBTest. I've run several custom scripts on DBTest, so now DBTest and DBTarget should be identical in every way (Tables, Values in tables, Columns, SPROCS, etc.)

Question: Is there an easy way to compare these two databases?

The only strategy I can think of is:

USE [DBTarget];
SELECT * FROM tblTableN;
USE [DBTest];
SELECT * FROM tblTableN;

I would have to do this for each table, and visually compare them myself. However, this seems very error prone and has a high chance of human error.

Does anyone know of a better way? I'm using TSQL in SQL Management Studio 2005.

Answer 1

A few options:

• Use one of the redgate products.

• Write a script to do a full outer join between each of the tables you want to compare, with comparison logic. Include system tables to check your structure etc is the same

• Script/query the database and the data, then do a text comparison.

Answer 2

Try this tool(its freeware):

DBComparer

Answer 3

http://www.red-gate.com/products/SQL_Data_Compare/index.htm for data http://www.red-gate.com/products/SQL_Compare/index.htm for schema