compare 2 database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

compare 2 database

Author

Topic

wided
Posting Yak Master

218 Posts

Posted - 2011-05-04 : 12:42:22

Hello,

I have 2 database
A database of work (development)
and another real database

I want to know if there is a system procedure that allows me to compare the two databases (table structure or stored procedure)

NB: In the 2 databases, tables and procedures with the same name

thanks

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-05-04 : 13:35:35

I am not aware of any procedure that can do this.

We use a tool - Red Gate's SQL Compare - it works pretty good.

But here is a way you can get by - Script the Databases by using the Generate scripts wizard. You can generate texts scripts separately for Tables, Stored Procs etc.. Then compare this files with a text compare tool or MS Word.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-04 : 14:55:33

DB is much more than just tables and SPs..... But if you want to focus only on the different between the two objects, you can do it. how much detail you can compare, depending on your expectation and time ....

First as denis_the_thief said, generating the scripts to create the two db on your local machine (I do this way), then compare in this, don't touch production and development enviroments.

Then

1-tables
From DMVs and sys SP, colect info of tables, then compare as the way you want

2-SPs
Compare the objects(in sys objects) with object type is SP to find out there are some new. If yes, create the new ones. Then generate scripts to drop-recreate the existing ones.

If you need more detail, post it here

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-05-04 : 20:00:03

We also use the RedGate SQL Compare tool. It works fine and saves us a ton of time. Other vendors have similar products so do some comparison shopping. If you do settle on RedeGate, my only suggestion is to get the bundle that has both the SQL Compare and SQL Data Compare. You probably won't use the Data Compare tool nearly as often but it can be a real life saver when you do.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

wided
Posting Yak Master

218 Posts

Posted - 2011-05-05 : 04:10:10

thank you

but how can i use "RedGate SQL Compare tool"

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-05 : 04:58:22

download trial version from
http://www.red-gate.com/products/sql-development/sql-compare/
and after installation just provide it, Source and Target Databases and just press Compare ;)

--------------------------
http://connectsql.blogspot.com/

wided
Posting Yak Master

218 Posts

Posted - 2011-05-05 : 05:07:17

thank you
it is ok

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-05-05 : 08:50:04

quote:
Originally posted by namman

...
1-tables
From DMVs and sys SP, colect info of tables, then compare as the way you want
...

Namman - just curious - what do you mean with reference to 'DMVs'? I didn't think there was table structure information there, I use DMVs for performance information.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources