SQLServerCentral / General / SQL Server 7,2000 / Compare two databases / Latest Posts

RE: Compare two databases

je.maguina — Sun, 20 Jan 2013 09:13:39 GMT

Here is a tutorial with a free tool included: http://testools.blogspot.com/2013/01/compare-2-sql-server-databases-very.html

RE: Compare two databases

yonision — Thu, 13 Sep 2012 14:23:41 GMT

This one is the most comprehensive, and also the cheapest (schema+data)[url=http://nobhillsoft.com/NHDBCompare.aspx]http://nobhillsoft.com/NHDBCompare.aspx[/url]

RE: Compare two databases

gprias — Mon, 18 Jun 2012 13:57:51 GMT

Below script is used to Compare fields and their data on two DB's.ALTER procedure [dbo].[DataComparision]@Server1 varchar(50),@Db1 varchar(50),@Server2 varchar(50),@Db2 varchar(50)ASBEGIN--Schemadiffer is a table that contains the details of field differed information and differed table information.Truncate table datacomparisonDECLARE @tbl varchar(255)DECLARE @sql varchar(max)DECLARE @tblList CURSORSET @tblList = CURSOR FOR-- Comparision table contains the list of tables thaqt should be used in production loading process.select tablename from cmpOPEN @tblListFETCH NEXTFROM @tblList INTO @tblWHILE @@FETCH_STATUS = 0BEGINset @sql='if exists(select 1 from ['+@Server1+'].['+@Db1+'].sys.tables where name='''+@tbl+''')if exists(select 1 from ['+@Server2+'].['+@Db2+'].sys.tables where name='''+@tbl+''')BEGINinsert into datacomparison(TableName,FieldName,HomeDB,HomeServer,RivalDB,RivalServer,Comments)select '''+@tbl+''' TableName,name FieldName,'''+@Db1+''' HomeDB,'''+@Server1+''' HomeServer,'''+@Db2+''' RivalDB,'''+@Server2+''' RivalServer,''Exists only in HomeDB'' Comments from ['+@Server1+'].['+@Db1+'].sys.columns where object_id in (select object_id from ['+@Server1+'].['+@Db1+'].sys.tables where name='''+@tbl+''') and name not in (select name from ['+@Server2+'].['+@Db2+'].sys.columns where object_id in (select object_id from ['+@Server2+'].['+@Db2+'].sys.tables where name='''+@tbl+'''))insert into datacomparison(TableName,FieldName,HomeDB,HomeServer,RivalDB,RivalServer,Comments)select '''+@tbl+''' TableName,name FieldName,'''+@Db1+''' HomeDB,'''+@Server1+''' HomeServer,'''+@Db2+''' RivalDB,'''+@Server2+''' RivalServer,''Exists only in RivalDB'' Comments from ['+@Server2+'].['+@Db2+'].sys.columns where object_id in (select object_id from ['+@Server2+'].['+@Db2+'].sys.tables where name='''+@tbl+''') and name not in (select name from ['+@Server1+'].['+@Db1+'].sys.columns where object_id in (select object_id from ['+@Server1+'].['+@Db1+'].sys.tables where name='''+@tbl+'''))DECLARE @cln varchar(255)DECLARE @sqlCln varchar(max)DECLARE @clnList CURSORSET @clnList = CURSOR FORselect name from ['+@Server1+'].['+@Db1+'].sys.columns where object_id in (select object_id from ['+@Server1+'].['+@Db1+'].sys.tables where name='''+@tbl+''')and name not in (select FieldName from datacomparison where TableName = '''+@tbl+''' and Comments is not NULL) OPEN @clnListFETCH NEXTFROM @clnList INTO @clnWHILE @@FETCH_STATUS = 0BEGINset @sqlCln = ''insert into datacomparison(TableName,FieldName,HomeDB,HomeServer,HomeCount,RivalDB,RivalServer,RivalCount,ColumnDiffernce)''set @sqlCln = @sqlCln + ''Select '''''+@tbl+''''' TableName,''''''+@cln+'''''' FieldName,'''''+@Db1+''''' HomeDB,'''''+@Server1+''''' HomeServer,(select count(*) from ['+@Server1+'].['+@Db1+'].dbo.['+@tbl+']) HomeCount,'''''+@Db2+''''' RivalDB,'''''+@Server2+''''' RivalServer,(select count(*) from ['+@Server2+'].['+@Db2+'].dbo.['+@tbl+']) RivalCount,count(*) ColumnDiffernce from (''set @sqlCln = @sqlCln+''select [''+@cln+''] from ['+@Server1+'].['+@Db1+'].dbo.['+@tbl+'] ''set @sqlCln = @sqlCln+''except ''set @sqlCln = @sqlCln+''select [''+@cln+''] from ['+@Server2+'].['+@Db2+'].dbo.['+@tbl+']) as a ''--print @sqlClnexec(@sqlCln)FETCH NEXTFROM @clnList INTO @clnENDCLOSE @clnListDEALLOCATE @clnListEND'exec(@sql)--print @sqlPRINT @tblFETCH NEXTFROM @tblList INTO @tblENDCLOSE @tblListDEALLOCATE @tblListENDThanksGanesh

RE: Compare two databases

GilaMonster — Mon, 19 Mar 2012 06:20:12 GMT

Link works just fine here: [url]http://msdn.microsoft.com/en-us/library/ms162843.aspx[/url]tablediff UtilitySQL Server 2008 R2 Seriously though, get RedGate's SQLCompare or SQLDataCompare

RE: Compare two databases

MSBI Learner — Mon, 19 Mar 2012 01:25:59 GMT

Link you've provided isn't working, can u pls provide the correct URL as I am in a need of comparing two DBs..

RE: Compare two databases

Ninja's_RGR'us — Wed, 24 Aug 2011 15:39:24 GMT

[quote][b]Steve Jones - SSC Editor (8/24/2011)[/b][hr]I believe that tablediff will do this. It's a free tool from MS, but it goes table by table. You could script the calls to have it check all tables for you[url]http://msdn.microsoft.com/en-us/library/ms162843.aspx[/url]Other than that, you should search around the site. I doubt anyone has a SQL 2000 script handy, as it's an older product and comparison isn't necessarily simple. Easy to make mistakes, and hard to debug. Much easier, and safer, for most companies do spend the $300 or so if they need this.[/quote]300$ for a tool like that is a total no brainer especially if you plan to release all year around.Doing anything close to what that tool does will take you far more than 300$ worth of time. Garanteed.

RE: Compare two databases

Steve Jones - SSC Editor — Wed, 24 Aug 2011 11:16:44 GMT

I believe that tablediff will do this. It's a free tool from MS, but it goes table by table. You could script the calls to have it check all tables for you[url]http://msdn.microsoft.com/en-us/library/ms162843.aspx[/url]Other than that, you should search around the site. I doubt anyone has a SQL 2000 script handy, as it's an older product and comparison isn't necessarily simple. Easy to make mistakes, and hard to debug. Much easier, and safer, for most companies do spend the $300 or so if they need this.

RE: Compare two databases

ravishankar.yedoti — Wed, 24 Aug 2011 11:03:02 GMT

can u provide me script, i don't want it do be done with tool's

RE: Compare two databases

Ninja's_RGR'us — Wed, 24 Aug 2011 10:17:09 GMT

[quote][b]ravishankar.yedoti (8/24/2011)[/b][hr]but , i require that to be worked now. Please provide me solution for that.[/quote]Next time please start a new thread. You're likelier to get more help faster (more people will see it).Also be careful about your wording, that phrase can be interpreted as "do my work now".I think this is not what you meant and that english is not your best language. So just keep that in mind for next time ;-).

RE: Compare two databases

GilaMonster — Wed, 24 Aug 2011 09:59:56 GMT

[url]http://www.red-gate.com/products/sql-development/sql-compare/[/url][url]http://www.red-gate.com/products/sql-development/sql-data-compare/[/url]

RE: Compare two databases

ravishankar.yedoti — Wed, 24 Aug 2011 09:38:58 GMT

but , i require that to be worked now. Please provide me solution for that.

RE: Compare two databases

GilaMonster — Wed, 24 Aug 2011 07:43:02 GMT

Please note, this thread is 8 years old.

RE: Compare two databases

shilpa.shankar87 — Wed, 24 Aug 2011 07:31:46 GMT

Im not able to see any of the scripts given above

RE: Compare two databases

Andrew Watson-478275 — Tue, 02 Sep 2008 09:06:39 GMT

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.

RE: Compare two databases

Abhijit More — Tue, 02 Sep 2008 03:27:57 GMT

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.

RE: Compare two databases

Jeff Moden — Mon, 01 Sep 2008 19:24:37 GMT

[quote][b]Khalidhussain (9/24/2003)[/b][hr]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 databasesTable structure in Development and Test databases (including identity seed and identity increment in each table)View name in Development and Test databasesView structure in Development and Test databasesStored procedure name in Development and Test databasesStored procedure structure in Development and Test databasesWe 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 [/quote]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.

RE: Compare two databases

Jeff Moden — Mon, 01 Sep 2008 19:21:20 GMT

[quote][b]G.R.Preethiviraj Kulasingham (9/25/2003)[/b][hr]I too faced your situation and finally wrote the scripts myself.I'll be happy if my scripts are usefullPlease check [url]http://www.sqlservercentral.com/scripts/listscriptsbyauthor.asp?author=1771[/url]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[/quote]The link doesn't work.

RE: Compare two databases

abhas1981-866406 — Mon, 01 Sep 2008 00:32:58 GMT

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....

RE: Compare two databases

Russ Bell — Thu, 25 Sep 2003 08:27:00 GMT

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.

RE: Compare two databases

Andy DBA — Thu, 25 Sep 2003 07:45:00 GMT

I've found Viktor Gorodnichenko's sp_CompareDB stored procedure to be very helpful.http://www.sql-server-performance.com/viktor_gorodnichenko.asp

RE: Compare two databases

Andy Warren — Thu, 25 Sep 2003 04:58:00 GMT

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!).Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Compare two databases

cneuhold — Thu, 25 Sep 2003 04:19:00 GMT

hi!concerning own scripts, you could maybe use the following as a starting point:http://www.sqlservercentral.com/scripts/contributions/246.aspbest regards,chris.

RE: Compare two databases

G.R.Prithiviraj Kulasingham — Thu, 25 Sep 2003 04:04:00 GMT

I too faced your situation and finally wrote the scripts myself.I'll be happy if my scripts are usefullPlease check [url]http://www.sqlservercentral.com/scripts/listscriptsbyauthor.asp?author=1771[/url]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

RE: Compare two databases

rregan — Wed, 24 Sep 2003 22:52:00 GMT

Here's a nice freeware tool:http://www.davidemauri.it/dabcos/default.aspx

RE: Compare two databases

wmp789 — Wed, 24 Sep 2003 16:36:00 GMT

We use Embarcadero Change Manager. Great tool!

RE: Compare two databases

joachim.verhagen — Wed, 24 Sep 2003 07:21:00 GMT

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_nameselect * from information_schema.views order by table_schema, table_nameselect routine_schema, routine_name, routine_definition from information_schema.routines order by routine_schema,routine_name

RE: Compare two databases

ThomasRushton — Wed, 24 Sep 2003 06:37:00 GMT

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.[url]http://www.adeptsql.com[/url]

Compare two databases

Khalidhussain — Wed, 24 Sep 2003 03:59:00 GMT

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 databasesTable structure in Development and Test databases (including identity seed and identity increment in each table)View name in Development and Test databasesView structure in Development and Test databasesStored procedure name in Development and Test databasesStored procedure structure in Development and Test databasesWe 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