Query to compare two DB's - 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 2000 Forums

SQL Server Development (2000)

Query to compare two DB's

Author

Topic

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2006-08-29 : 16:54:00

Hi All,

I need way to compare two databases. I was wondering if anyone has any way to compare databases.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-29 : 16:59:55

We use Red Gate's SQL Compare. It works great for database comparisons. If you want to compare data, then they also have SQL Data Compare.

Tara Kizer

srinath
Starting Member

16 Posts

Posted - 2006-08-31 : 00:25:41

Previously when I was searching thru google i find the below... I saved it But I don't remind exactly wher I got this...Try This if it works for you...

CReate PROCEDURE dbo.CompareDatabases(@databaseA varchar(50), @databaseB varchar(50))
AS
declare @strSQL varchar(200), @strTmp varchar(50), @nLoop int
set nocount on
create table #tmp_table (table_names varchar(50))
set @strSQL = 'insert into #tmp_table (table_names) select table_name from ' + @databaseA + '.information_schema.tables where table_name = ''lu_bu_area'''
--print @strSQL
exec(@strSQL)
declare crsr cursor local fast_forward for select * from #tmp_table
set @nLoop = 0
open crsr
fetch next from crsr into @strTmp
while (@@fetch_status=00)
begin
--print 'exec CompareTables ''' + @databaseA + ''', ''' + @databaseB + ''', ''' + @strTmp + ''''
--exec CompareTables @databaseA, @databaseB, @strTmp
--IF @tableB = '' SET @tableB = @tableA
declare @col_namesA varchar(4000), @prim_key varchar(50)
begin
--exec DBO.GetColumnNamesString @databaseA, @tableA, @col_namesA output, @prim_key output
set nocount on
create table #tmp_table1 (col_names varchar(50))
set @strSQL = 'insert into #tmp_table1 (col_names) select column_name from ' + @databaseB + '.information_schema.columns where
Table_name = ''' + @strTmp + ''''
PRINT @strTmp
exec(@strSQL)
set @col_namesA = ''
select @col_namesA = @col_namesA + CASE WHEN LEN(@col_namesA)>0 THEN ', ' ELSE '' END + '[' + col_names + ']' from #tmp_table1
set @prim_key = (select top 1 col_names from #tmp_table1)
set nocount off
--drop table #tmp_table1
end
--print @col_namesA
begin
set @strSQL = 'SELECT min(TableName) as TableName , ' + @col_namesA + '
FROM (
SELECT ''New_' + @strTmp + ''' as TableName, A.*
FROM ' + @databaseA + '.dbo.' + @strTmp + ' A
UNION ALL
SELECT ''Old_' + @strTmp + ''' as TableName, B.*
FROM ' + @databaseB + '.dbo.' + @strTmp + ' B
) tmp
GROUP BY ' + @col_namesA + '
HAVING COUNT(*) = 1
ORDER BY ' + @prim_key
print @strSQL
exec (@strSQL)
end
fetch next from crsr into @strTmp
set @nLoop = @nLoop + 1
end -- loop
close crsr
deallocate crsr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources