Compare two databases

Question

Compare two databases

Khalidhussain

Right there with Babe

Points: 764
More actions
September 24, 2003 at 3:59 am

#160152

I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is there a way to create a SQL view, which can do this job for me?
I want to compare following:
Table name in Development and Test databases
Table structure in Development and Test databases (including identity seed and identity increment in each table)
View name in Development and Test databases
View structure in Development and Test databases
Stored procedure name in Development and Test databases
Stored procedure structure in Development and Test databases
We have ‘system data’ tables they hold data, which we need in our VB6 system.
These tables can be identify as prefix ‘sysd’ and table name e.g. sysdStatus hold all possible statuses for a policy.
I need to compare those system data in Development and Test databases as well.
Thank you

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 1

quote:
I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is there a way to create a SQL view, which can do this job for me?

We use AdeptSQL's SQLDiff tool for the database structures, and it's not overly expensive. They also do a tool to compare data within tables.

http://www.adeptsql.com

Thomas Rushton
blog: https://thelonedba.wordpress.com

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 2

You can write a script that makes select statements from the information_schema views and do a windiff between the results.

Something like:

select * from information_schema.columns order by table_schema,table_name, column_name

select * from information_schema.views order by table_schema, table_name

select routine_schema, routine_name, routine_definition from information_schema.routines order by routine_schema,routine_name

wmp789 SSCarpal Tunnel Points: 4354 More actions · Answer 3

wmp789

SSCarpal Tunnel

Points: 4354

September 24, 2003 at 4:36 pm

#475689

We use Embarcadero Change Manager. Great tool!

rregan SSC Enthusiast Points: 134 More actions · Answer 4

Here's a nice freeware tool:

http://www.davidemauri.it/dabcos/default.aspx

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 5

I too faced your situation and finally wrote the scripts myself.

I'll be happy if my scripts are usefull

Please check

http://www.sqlservercentral.com/scripts/listscriptsbyauthor.asp?author=1771

There are many wonderfull tools available in the market. But for my need, any where I can control, scripts are the best.

Cheers,

Preethiviraj Kulasingham

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

cneuhold Hall of Fame Points: 3375 More actions · Answer 6

hi!

concerning own scripts, you could maybe use the following as a starting point:

http://www.sqlservercentral.com/scripts/contributions/246.asp

best regards,

chris.

Andy Warren SSC Guru Points: 119902 More actions · Answer 7

Two other products in the space that I know of, one from Lockwood, one from Red Gate (along with Adept, all three advertise with us!).

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Andy DBA SSCommitted Points: 1776 More actions · Answer 8

I've found Viktor Gorodnichenko's sp_CompareDB stored procedure to be very helpful.

http://www.sql-server-performance.com/viktor_gorodnichenko.asp

Russ Bell SSC Enthusiast Points: 192 More actions · Answer 9

We had had great success using Red-Gate comparison tools.

http://www.red-gate.com/sql/summary.htm

Very affordable and can product a script to syncronize in either direction.

abhas1981-866406 Grasshopper Points: 13 More actions · Answer 10

Hi Viktor,

I have tried ur URL but it seems that URL is not active now. Will u please send the script of ur Sp_compare DB database....

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

G.R.Preethiviraj Kulasingham (9/25/2003)
I too faced your situation and finally wrote the scripts myself.
I'll be happy if my scripts are usefull
Please check
http://www.sqlservercentral.com/scripts/listscriptsbyauthor.asp?author=1771
There are many wonderfull tools available in the market. But for my need, any where I can control, scripts are the best.
Cheers,
Preethiviraj Kulasingham

The link doesn't work.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Khalidhussain (9/24/2003)
I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is there a way to create a SQL view, which can do this job for me?
I want to compare following:
Table name in Development and Test databases
Table structure in Development and Test databases (including identity seed and identity increment in each table)
View name in Development and Test databases
View structure in Development and Test databases
Stored procedure name in Development and Test databases
Stored procedure structure in Development and Test databases
We have ‘system data’ tables they hold data, which we need in our VB6 system.
These tables can be identify as prefix ‘sysd’ and table name e.g. sysdStatus hold all possible statuses for a policy.
I need to compare those system data in Development and Test databases as well.
Thank you

I don't see anything where you talk about what you would do if they're different. It you want to make sure they are the same, snapshot one over the other.

Now, if you want to identify the differences, then you'll need something like SQL Compare and Data Compare from Redgate. Yes, you could write your own... it will cost you more to do that both in the form of errors and total hours/dolarrs spent than just buying the product which has been tested and tested and retested.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Abhijit More SSCertifiable Points: 6897 More actions · Answer 13

Hi Khalidhussain,

Instead of wasting your time in writing a script for comparing database objects, I'll suggest to to better go for third party tool available.

Abhijit - http://abhijitmore.wordpress.com

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 14

I know you mention that your systems are VB6, but if you also do development on later versions (2005 onwards) of Visual Studio (I think it has to be VS Team System), these have a schema compare tool available, which works after a fashion.