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