August 24, 2011 at 7:31 am
Im not able to see any of the scripts given above
August 24, 2011 at 7:43 am
Please note, this thread is 8 years old.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2011 at 9:38 am
but , i require that to be worked now. Please provide me solution for that.
August 24, 2011 at 9:59 am
http://www.red-gate.com/products/sql-development/sql-compare/
http://www.red-gate.com/products/sql-development/sql-data-compare/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2011 at 10:17 am
ravishankar.yedoti (8/24/2011)
but , i require that to be worked now. Please provide me solution for that.
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 ;-).
August 24, 2011 at 11:03 am
can u provide me script, i don't want it do be done with tool's
August 24, 2011 at 11:16 am
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
http://msdn.microsoft.com/en-us/library/ms162843.aspx
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.
August 24, 2011 at 3:39 pm
Steve Jones - SSC Editor (8/24/2011)
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 youhttp://msdn.microsoft.com/en-us/library/ms162843.aspx
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.
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.
March 19, 2012 at 1:26 am
Link you've provided isn't working, can u pls provide the correct URL as I am in a need of comparing two DBs..
March 19, 2012 at 6:20 am
Link works just fine here: http://msdn.microsoft.com/en-us/library/ms162843.aspx
tablediff Utility
SQL Server 2008 R2
Seriously though, get RedGate's SQLCompare or SQLDataCompare
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2012 at 1:57 pm
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)
AS
BEGIN
--Schemadiffer is a table that contains the details of field differed information and differed table information.
Truncate table datacomparison
DECLARE @tbl varchar(255)
DECLARE @sql varchar(max)
DECLARE @tblList CURSOR
SET @tblList = CURSOR FOR
-- Comparision table contains the list of tables thaqt should be used in production loading process.
select tablename from cmp
OPEN @tblList
FETCH NEXT
FROM @tblList INTO @tbl
WHILE @@FETCH_STATUS = 0
BEGIN
set @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+''')
BEGIN
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 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 CURSOR
SET @clnList = CURSOR FOR
select 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 @clnList
FETCH NEXT
FROM @clnList INTO @cln
WHILE @@FETCH_STATUS = 0
BEGIN
set @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 @sqlCln
exec(@sqlCln)
FETCH NEXT
FROM @clnList INTO @cln
END
CLOSE @clnList
DEALLOCATE @clnList
END'
exec(@sql)
--print @sql
PRINT @tbl
FETCH NEXT
FROM @tblList INTO @tbl
END
CLOSE @tblList
DEALLOCATE @tblList
END
Thanks
Ganesh
September 13, 2012 at 2:23 pm
This one is the most comprehensive, and also the cheapest (schema+data)
January 20, 2013 at 9:13 am
Here is a tutorial with a free tool included: http://testools.blogspot.com/2013/01/compare-2-sql-server-databases-very.html
July 8, 2013 at 3:03 am
Hi
This link is not working. Could you please send me this script in gmail please.
Thanks
NU
May 5, 2016 at 2:41 am
Dear friend,
i need to compare two database, like Production and dev dbs, could you plz share query if you have...thanks in advance
kumar
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply