June 6, 2012 at 4:39 pm
------------------------------------
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
---------------------------
Can anyone pleases help me to create this script without using cursor?
Thanks
Ganesh
June 11, 2012 at 7:26 am
I have two Data Bases in my PC.One is staging and another one is Development. Same tables are existing in both Development DB and Staging DB.For example development DB has a table called as 'Students'. This same table are also existing in Staging also.Both tables can have same number of fields or different number of fields.(Development 'Students' table has a field 'Ranks'. But Staging 'Students' table don't have the 'Rank' field.)Also the both tables data may be differed or same.
Here i want to do the following operations,
1. Compare both DB's that have the same tables. If any table doesn't exist in any DB i have to store the result in a result table.
2. Then i have to compare the fields that are same of different in both development and staging DB.And store the results into result table.
3. Finally i have to compare the data that are existing in both tables. Also store this results in to result table.
Above script is doing this operation exactly. But Problem is i am using Cursor to do the operation. It takes too long to complete the operation and also it is facing time out error or linked server connection error, since i both DB's are existing in different servers.
Can you please any one give the code or idea to do the same thing without using cursor.
Thanks
Ganesh
June 11, 2012 at 7:36 am
I recommend RedGate's SQLCompare and 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 12, 2012 at 10:28 am
Thanks FYI. But it is not free ware. Is there any way to do using script?
Ganesh
June 12, 2012 at 10:43 am
Sure, but it's going to take ages to write and debug (and you're pretty much on the right track with the code posted).
Compare how much the product costs with how many hours it'll take you to write something from scratch and then see which is cheaper. I bet anything it'll be the product.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply