SQL Server Performance

sp_CompareDB

Discussion in 'Contribute Your SQL Server Scripts' started by gaurav_bindlish, Jul 6, 2003.

  1. gaurav_bindlish New Member

    Sometime ago I came across this script to compare two databases.<pre>USE Master<br />GO<br />IF EXISTS (SELECT * FROM sysobjects WHERE name = 'dbp_CompareDB' and type = 'P')<br />DROP PROC dbp_CompareDB<br />GO<br />--------------------------------------------------------------------------------------------<br />-- sp_CompareDB<br />-- <br />-- The SP compares structures and data in 2 databases.<br />-- 1. Compares if all tables in one database have analog (by name) in second database<br />-- Tables not existing in one of databases won't be used for data comparing<br />-- 2. Compares if structures for tables with the same names are the same. Shows structural<br />-- differences like:<br />-- authors<br />-- Column Phone: in db1 - char(12), in db2 - char(14)<br />-- sales<br />-- Column Location not in db2<br />-- Tables, having different structures, won't be used for data comparing. However if the tables<br />-- contain columns of the same type and different length (like Phone in the example above) or<br />-- tables have compatible data types (have the same type in syscolumns - char and nchar, <br />-- varchar and nvarchar etc) they will be allowed for data comparing.<br />-- 3. Data comparison itself. <br />-- 3.1 Get information about unique keys in the tables. If there are unique keys then one of them<br />-- (PK is a highest priority candidate for this role) will be used to specify rows with<br />-- different data.<br />-- 3.2 Get information about all data columns in the table and form predicates that will be <br />-- used to compare data.<br />-- 3.3 Compare data with the criteria:<br />-- a. if some unique keys from the table from first database do not exist in second db (only<br />-- for tables with a unique key)<br />-- b. if some unique keys from the table from second database do not exist in first db (only<br />-- for tables with a unique key)<br />-- c. if there are rows with the same values of unique keys and different data in other<br />-- columns (only for tables with a unique key)<br />-- d. if there are rows in the table from first database that don't have a twin in the <br />-- table from second db<br />-- e. if there are rows in the table from second database that don't have a twin in the <br />-- table from first db<br />--------------------------------------------------------------------------------------------<br />-- Parameters:<br />-- 1. @db1 - name of first database to compare<br />-- 2. @db2 - name of second database to compare<br />-- 3. @TabList - list of tables to compare. if empty - all tables in the databases should be<br />-- compared<br />-- 4. @NumbToShow - number of rows with differences to show. Default - 10.<br />-- 5. @OnlyStructure - flag, if set to 1, allows to avoid data comparing. Only structures should<br />-- be compared. Default - 0<br />-- 6. @NoTimestamp - flag, if set to 1, allows to avoid comparing of columns of timestamp<br />-- data type. Default - 0<br />-- 7. @VerboseLevel - if set to 1 allows to print querues used for data comparison<br />--------------------------------------------------------------------------------------------<br />-- Created by Viktor Gorodnichenko (c)<br />-- Created on: July 5, 2001<br />--------------------------------------------------------------------------------------------<br />CREATE PROC dbp_CompareDB<br />@db1 varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />@db2 varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />@OnlyStructure bit = 0,<br />@TabList varchar(8000) = '',<br />@NumbToShow int = 10,<br />@NoTimestamp bit = 0,<br />@VerboseLevel tinyint = 0<br />AS<br />if @OnlyStructure &lt;&gt; 0<br />set @OnlyStructure = 1<br />if @NoTimestamp &lt;&gt; 0<br />set @NoTimestamp = 1<br />if @VerboseLevel &lt;&gt; 0<br />set @VerboseLevel = 1<br /><br />SET NOCOUNT ON<br />SET ANSI_WARNINGS ON<br />SET ANSI_NULLS ON<br />declare @sqlStr varchar(8000)<br />set nocount on<br />-- Checking if there are specified databases<br />declare @SrvName sysname<br />declare @DBName sysname<br />set @db1 = RTRIM(LTRIM(@db1))<br />set @db2 = RTRIM(LTRIM(@db2))<br />set @SrvName = @@SERVERNAME<br />if CHARINDEX('.',@db1) &gt; 0<br />begin<br />set @SrvName = LEFT(@db1,CHARINDEX('.',@db1)-1)<br />if not exists (select * from master.dbo.sysservers where srvname = @SrvName)<br />begin<br />print 'There is no linked server named '+@SrvName+'. End of work.'<br />return <br />end<br />set @DBName = RIGHT(@db1,LEN(@db1)-CHARINDEX('.',@db1))<br />end<br />else<br />set @DBName = @db1<br />exec ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')<br />if @@rowcount = 0<br />begin<br />print 'There is no database named '+@db1+'. End of work.'<br />return <br />end<br />set @SrvName = @@SERVERNAME<br />if CHARINDEX('.',@db2) &gt; 0<br />begin<br />set @SrvName = LEFT(@db2,CHARINDEX('.',@db2)-1)<br />if not exists (select * from master.dbo.sysservers where srvname = @SrvName)<br />begin<br />print 'There is no linked server named '+@SrvName+'. End of work.'<br />return <br />end<br />set @DBName = RIGHT(@db2,LEN(@db2)-CHARINDEX('.',@db2))<br />end<br />else<br />set @DBName = @db2<br />exec ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')<br />if @@rowcount = 0<br />begin<br />print 'There is no database named '+@db2+'. End of work.'<br />return <br />end<br /><br />print Replicate('-',LEN(@db1)+LEN(@db2)+25)<br />print 'Comparing databases '+@db1+' and '+@db2<br />print Replicate('-',LEN(@db1)+LEN(@db2)+25)<br />print 'Options specified:'<br />print ' Compare only structures: '+CASE WHEN @OnlyStructure = 0 THEN 'No' ELSE 'Yes' END<br />print ' List of tables to compare: '+CASE WHEN LEN(@TabList) = 0 THEN ' All tables' ELSE @TabList END<br />print ' Max number of different rows in each table to show: '+LTRIM(STR(@NumbToShow))<br />print ' Compare timestamp columns: '+CASE WHEN @NoTimestamp = 0 THEN 'No' ELSE 'Yes' END<br />print ' Verbose level: '+CASE WHEN @VerboseLevel = 0 THEN 'Low' ELSE 'High' END<br /><br />-----------------------------------------------------------------------------------------<br />-- Comparing structures<br />-----------------------------------------------------------------------------------------<br />print CHAR(10)+Replicate('-',36)<br />print 'Comparing structure of the databases'<br />print Replicate('-',36)<br />if exists (select * from tempdb.dbo.sysobjects where name like '#TabToCheck%')<br />drop table #TabToCheck<br />create table #TabToCheck (name sysname)<br />declare @NextCommaPos int<br />if len(@TabList) &gt; 0 <br />begin<br />while 1=1<br />begin<br />set @NextCommaPos = CHARINDEX(',',@TabList)<br />if @NextCommaPos = 0<br />begin<br />set @sqlstr = 'insert into #TabToCheck values('''+@TabList+''')'<br />exec (@sqlstr)<br />break<br />end<br />set @sqlstr = 'insert into #TabToCheck values('''+LEFT(@TabList,@NextCommaPos-1)+''')'<br />exec (@sqlstr)<br />set @TabList = RIGHT(@TabList,LEN(@TabList)-@NextCommaPos)<br />end<br />end<br />else -- then will check all tables<br />begin<br />exec ('insert into #TabToCheck select name from '+@db1+'.dbo.sysobjects where type = ''U''')<br />exec ('insert into #TabToCheck select name from '+@db2+'.dbo.sysobjects where type = ''U''')<br />end<br />-- First check if at least one table specified in @TabList exists in db1<br />exec ('declare @Name sysname select @Name=name from '+@db1+'.dbo.sysobjects where name in (select * from #TabToCheck)')<br />if @@rowcount = 0<br />begin<br />print 'No tables in '+@db1+' to check. End of work.'<br />return<br />end<br />-- Check if tables existing in db1 are in db2 (all tables or specified in @TabList)<br />if exists (select * from tempdb.dbo.sysobjects where name like '#TabNotInDB2%')<br />drop table #TabNotInDB2<br />create table #TabNotInDB2 (name sysname)<br />insert into #TabNotInDB2 <br />exec ('select name from '+@db1+'.dbo.sysobjects d1o '+<br />'where name in (select * from #TabToCheck) and '+<br />' d1o.type = ''U'' and not exists '+<br />'(select * from '+@db2+'.dbo.sysobjects d2o'+<br />' where d2o.type = ''U'' and d2o.name = d1o.name)')<br />if @@rowcount &gt; 0<br />begin<br />print CHAR(10)+'The table(s) exist in '+@db1+', but do not exist in '+@db2+':'<br />select * from #TabNotInDB2 <br />end<br />delete from #TabToCheck where name in (select * from #TabNotInDB2)<br />drop table #TabNotInDB2<br /><br />if exists (select * from tempdb.dbo.sysobjects where name like '#TabNotInDB1%')<br />drop table #TabNotInDB1<br />create table #TabNotInDB1 (name sysname)<br />insert into #TabNotInDB1 <br />exec ('select name from '+@db2+'.dbo.sysobjects d1o '+<br />'where name in (select * from #TabToCheck) and '+<br />' d1o.type = ''U'' and not exists '+<br />'(select * from '+@db1+'.dbo.sysobjects d2o'+<br />' where d2o.type = ''U'' and d2o.name = d1o.name)')<br />if @@rowcount &gt; 0<br />begin<br />print CHAR(10)+'The table(s) exist in '+@db2+', but do not exist in '+@db1+':'<br />select * from #TabNotInDB1 <br />end<br />delete from #TabToCheck where name in (select * from #TabNotInDB1)<br />drop table #TabNotInDB1<br />-- Comparing structures of tables existing in both dbs<br />print CHAR(10)+'Checking if there are tables existing in both databases having structural differences ...'+CHAR(10)<br />if exists (select * from tempdb.dbo.sysobjects where name like '#DiffStructure%')<br />drop table #DiffStructure<br />create table #DiffStructure (name sysname)<br />set @sqlStr='<br />declare @TName1 sysname, @TName2 sysname, @CName1 sysname, @CName2 sysname,<br />@TypeName1 sysname, @TypeName2 sysname,<br />@CLen1 smallint, @CLen2 smallint, @Type1 sysname, @Type2 sysname, @PrevTName sysname<br />declare @DiffStructure bit<br />declare Diff cursor fast_forward for<br />select d1o.name, d2o.name, d1c.name, d2c.name, d1t.name, d2t.name,<br />d1c.length, d2c.length, d1c.type, d2c.type<br />from ('+@db1+'.dbo.sysobjects d1o <br />JOIN '+@db2+'.dbo.sysobjects d2o2 ON d1o.name = d2o2.name and d1o.type = ''U'' --only tables in both dbs<br />and d1o.name in (select * from #TabToCheck)<br />JOIN '+@db1+'.dbo.syscolumns d1c ON d1o.id = d1c.id<br />JOIN '+@db1+'.dbo.systypes d1t ON d1c.xusertype = d1t.xusertype)<br />FULL JOIN ('+@db2+'.dbo.sysobjects d2o <br />JOIN '+@db1+'.dbo.sysobjects d1o2 ON d1o2.name = d2o.name and d2o.type = ''U'' --only tables in both dbs<br />and d2o.name in (select * from #TabToCheck)<br />JOIN '+@db2+'.dbo.syscolumns d2c ON d2c.id = d2o.id<br />JOIN '+@db2+'.dbo.systypes d2t ON d2c.xusertype = d2t.xusertype)<br />ON d1o.name = d2o.name and d1c.name = d2c.name<br />WHERE (not exists <br />(select * from '+@db2+'.dbo.sysobjects d2o2<br />JOIN '+@db2+'.dbo.syscolumns d2c2 ON d2o2.id = d2c2.id<br />JOIN '+@db2+'.dbo.systypes d2t2 ON d2c2.xusertype = d2t2.xusertype<br />where d2o2.type = ''U''<br />and d2o2.name = d1o.name <br />and d2c2.name = d1c.name <br />and d2t2.name = d1t.name<br />and d2c2.length = d1c.length)<br />OR not exists <br />(select * from '+@db1+'.dbo.sysobjects d1o2<br />JOIN '+@db1+'.dbo.syscolumns d1c2 ON d1o2.id = d1c2.id<br />JOIN '+@db1+'.dbo.systypes d1t2 ON d1c2.xusertype = d1t2.xusertype<br />where d1o2.type = ''U''<br />and d1o2.name = d2o.name <br />and d1c2.name = d2c.name <br />and d1t2.name = d2t.name<br />and d1c2.length = d2c.length))<br />order by coalesce(d1o.name,d2o.name), d1c.name<br />open Diff<br />fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,<br />@CLen1, @CLen2, @Type1, @Type2<br />set @PrevTName = ''''<br />set @DiffStructure = 0<br />while @@fetch_status = 0<br />begin<br />if Coalesce(@TName1,@TName2) &lt;&gt; @PrevTName<br />begin<br />if @PrevTName &lt;&gt; '''' and @DiffStructure = 1<br />begin<br />insert into #DiffStructure values (@PrevTName)<br />set @DiffStructure = 0<br />end<br />set @PrevTName = Coalesce(@TName1,@TName2)<br />print @PrevTName<br />end<br />if @CName2 is null<br />print '' Colimn ''+RTRIM(@CName1)+'' not in '+@db2+'''<br />else<br />if @CName1 is null<br />print '' Colimn ''+RTRIM(@CName2)+'' not in '+@db1+'''<br />else<br />if @TypeName1 &lt;&gt; @TypeName2<br />print '' Colimn ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+'', in '+@db2+' - ''+RTRIM(@TypeName2)<br />else --the columns are not null(are in both dbs) and types are equal,then length are diff<br />print '' Colimn ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+''(''+<br />LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen1/2 else @CLen1 end))+<br />''), in '+@db2+' - ''+RTRIM(@TypeName2)+''(''+<br />LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen2/2 else @CLen2 end))+'')''<br />if @Type1 = @Type2<br />set @DiffStructure=@DiffStructure -- Do nothing. Cannot invert predicate<br />else<br />set @DiffStructure = 1<br />fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,<br />@CLen1, @CLen2, @Type1, @Type2<br />end<br />deallocate Diff<br />if @DiffStructure = 1<br />insert into #DiffStructure values (@PrevTName)<br />'<br />exec (@sqlStr)<br />if (select count(*) from #DiffStructure) &gt; 0<br />begin<br />print CHAR(10)+'The table(s) have the same name and different structure in the databases:'<br />select distinct * from #DiffStructure <br />delete from #TabToCheck where name in (select * from #DiffStructure)<br />end<br />else<br />print CHAR(10)+'There are no tables with the same name and structural differences in the databases'+CHAR(10)+CHAR(10)<br />if @OnlyStructure = 1<br />begin<br />print 'The option ''Only compare structures'' was specified. End of work.'<br />return<br />end<br />exec ('declare @Name sysname select @Name=d1o.name<br />from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o <br />where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''<br />and d1o.name not in (''dtproperties'') <br />and d1o.name in (select * from #TabToCheck)')<br />if @@rowcount = 0<br />begin<br />print 'There are no tables with the same name and structure in the databases to compare. End of work.'<br />return<br />end<br /><br /><br />-----------------------------------------------------------------------------------------<br />-- Comparing data <br />-----------------------------------------------------------------------------------------<br />-- ##CompareStr - will be used to pass comparing strings into dynamic script<br />-- to execute the string<br />if exists (select * from tempdb.dbo.sysobjects where name like '##CompareStr%')<br />drop table ##CompareStr<br />create table ##CompareStr (Ind int, CompareStr varchar(8000))<br /><br />if exists (select * from tempdb.dbo.sysobjects where name like '#DiffTables%')<br />drop table #DiffTables<br />create table #DiffTables (Name sysname)<br />if exists (select * from tempdb.dbo.sysobjects where name like '#IdenticalTables%')<br />drop table #IdenticalTables<br />create table #IdenticalTables (Name sysname)<br />if exists (select * from tempdb.dbo.sysobjects where name like '#EmptyTables%')<br />drop table #EmptyTables<br />create table #EmptyTables (Name sysname)<br />if exists (select * from tempdb.dbo.sysobjects where name like '#NoPKTables%')<br />drop table #NoPKTables<br />create table #NoPKTables (Name sysname)<br /><br />if exists (select * from tempdb.dbo.sysobjects where name like '#IndList1%')<br />truncate table #IndList1<br />else <br />create table #IndList1 (IndId int, IndStatus int,<br />KeyAndStr varchar(7000), KeyCommaStr varchar(1000))<br />if exists (select * from tempdb.dbo.sysobjects where name like '#IndList2%')<br />truncate table #IndList2<br />else<br />create table #IndList2 (IndId smallint, IndStatus int,<br />KeyAndStr varchar(7000), KeyCommaStr varchar(1000))<br /><br />print Replicate('-',51)<br />print 'Comparing data in tables with indentical structure:'<br />print Replicate('-',51)<br />--------------------------------------------------------------------------------------------<br />-- Cursor for all tables in dbs (or for all specified tables if parameter @TabList is passed)<br />--------------------------------------------------------------------------------------------<br />declare @SqlStrGetListOfKeys1 varchar(8000)<br />declare @SqlStrGetListOfKeys2 varchar(8000)<br />declare @SqlStrGetListOfColumns varchar(8000)<br />declare @SqlStrCompareUKeyTables varchar(8000)<br />declare @SqlStrCompareNonUKeyTables varchar(8000)<br />set @SqlStrGetListOfKeys1 = '<br />declare @sqlStr varchar(8000)<br />declare @ExecSqlStr varchar(8000)<br />declare @PrintSqlStr varchar(8000)<br />declare @Tab varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />declare @d1User varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />declare @d2User varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />declare @KeyAndStr varchar(8000) <br />declare @KeyCommaStr varchar(8000) <br />declare @AndStr varchar(8000) <br />declare @Eq varchar(8000) <br />declare @IndId int<br />declare @IndStatus int<br />declare @CurrIndId smallint<br />declare @CurrStatus int<br />declare @UKey sysname <br />declare @Col varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />declare @LastUsedCol varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />declare @xType int<br />declare @Len int<br />declare @SelectStr varchar(8000) <br />declare @ExecSql nvarchar(1000) <br />declare @NotInDB1 bit <br />declare @NotInDB2 bit <br />declare @NotEq bit <br />declare @Numb int<br />declare @Cnt1 int<br />declare @Cnt2 int<br />set @Numb = 0<br /><br />declare @StrInd int<br />declare @i int<br />declare @PrintStr varchar(8000)<br />declare @ExecStr varchar(8000)<br />declare TabCur cursor for <br /><br />select d1o.name, d1u.name, d2u.name from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o,<br />'+@db1+'.dbo.sysusers d1u, '+@db2+'.dbo.sysusers d2u <br />where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''<br />and d1o.uid = d1u.uid and d2o.uid = d2u.uid <br />and d1o.name not in (''dtproperties'') <br />and d1o.name in (select * from #TabToCheck)<br />order by 1<br /><br />open TabCur <br />fetch next from TabCur into @Tab, @d1User, @d2User <br />while @@fetch_status = 0 <br />begin <br />set @Numb = @Numb + 1<br />print Char(13)+Char(10)+LTRIM(STR(@Numb))+''. TABLE: [''+@Tab+''] ''<br /><br />set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db1+'.[''+@d1User+''].[''+@Tab+'']''<br />exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt1 output<br />print CHAR(10)+STR(@Cnt1)+'' rows in '+@db1+'''<br />set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db2+'.[''+@d2User+''].[''+@Tab+'']''<br />exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt2 output<br />print STR(@Cnt2)+'' rows in '+@db2+'''<br />if @Cnt1 = 0 and @Cnt2 = 0<br />begin<br />exec ('' insert into #EmptyTables values(''''[''+@Tab+'']'''')'') <br />goto NextTab<br />end<br />set @KeyAndStr = '''' <br />set @KeyCommaStr = '''' <br />set @NotInDB1 = 0<br />set @NotInDB2 = 0 <br />set @NotEq = 0<br />set @KeyAndStr = '''' <br />set @KeyCommaStr = '''' <br />truncate table #IndList1<br />declare UKeys cursor fast_forward for <br />select i.indid, i.status, c.name, c.xType from '+@db1+'.dbo.sysobjects o, '+@db1+'.dbo.sysindexes i, '+@db1+'.dbo.sysindexkeys k, '+@db1+'.dbo.syscolumns c <br />where i.id = o.id and o.name = @Tab<br />and (i.status & 2)&lt;&gt;0 <br />and k.id = o.id and k.indid = i.indid <br />and c.id = o.id and c.colid = k.colid <br />order by i.indid, c.name<br />open UKeys <br />fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType<br />set @CurrIndId = @IndId<br />set @CurrStatus = @IndStatus<br />while @@fetch_status = 0 <br />begin <br />if @KeyAndStr &lt;&gt; ''''<br />begin <br />set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10) <br />set @KeyCommaStr = @KeyCommaStr + '', '' <br />end <br />if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar<br />begin<br />set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''<br />end<br />if @xType = 173 or @xType = 165 -- binary, varbinary<br />begin<br />set @KeyAndStr = @KeyAndStr +<br />'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''<br />end<br />else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney<br />begin<br />set @KeyAndStr = @KeyAndStr + <br />'' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''<br />end<br />else if @xType = 106 or @xType = 108 -- int, decimal, numeric<br />begin<br />set @KeyAndStr = @KeyAndStr + <br />'' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''<br />end<br />else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real<br />begin <br />set @KeyAndStr = @KeyAndStr + <br />'' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''<br />end<br />else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit<br />begin<br />set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''<br />end<br />else if @xType = 36 -- 36 - id <br />begin<br />set @KeyAndStr = @KeyAndStr +<br />'' CASE WHEN d1.[''+@UKey+''] is null''+<br />'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+<br />'' ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null''+<br />'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+<br />'' ELSE d2.[''+@UKey+''] END''<br />end<br />else if @xType = 61 or @xType = 58 -- datetime, smalldatetime<br />begin<br />set @KeyAndStr = @KeyAndStr +<br />'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''<br />end<br />else if @xType = 189 -- timestamp (189) <br />begin<br />set @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''<br />end<br />else if @xType = 98 -- SQL_variant<br />begin<br />set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''<br />end<br />set @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey <br />fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType<br />if @IndId &lt;&gt; @CurrIndId<br />begin<br />insert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)<br />set @CurrIndId = @IndId<br />set @CurrStatus = @IndStatus<br />set @KeyAndStr = ''''<br />set @KeyCommaStr = '''' <br />end<br />end <br />deallocate UKeys <br />insert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)'<br />set @SqlStrGetListOfKeys2 = '<br />set @KeyAndStr = '''' <br />set @KeyCommaStr = '''' <br />truncate table #IndList2<br />declare UKeys cursor fast_forward for <br />select i.indid, i.status, c.name, c.xType from '+@db2+'.dbo.sysobjects o, '+@db2+'.dbo.sysindexes i, '+@db2+'.dbo.sysindexkeys k, '+@db2+'.dbo.syscolumns c <br />where i.id = o.id and o.name = @Tab<br />and (i.status & 2)&lt;&gt;0 <br />and k.id = o.id and k.indid = i.indid <br />and c.id = o.id and c.colid = k.colid <br />order by i.indid, c.name<br />open UKeys <br />fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType<br />set @CurrIndId = @IndId<br />set @CurrStatus = @IndStatus<br />while @@fetch_status = 0 <br />begin <br />if @KeyAndStr &lt;&gt; ''''<br />begin <br />set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10) <br />set @KeyCommaStr = @KeyCommaStr + '', '' <br />end <br />if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar<br />begin<br />set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''<br />end<br />if @xType = 173 or @xType = 165 -- binary, varbinary<br />begin<br />set @KeyAndStr = @KeyAndStr +<br />'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''<br />end<br />else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney<br />begin<br />set @KeyAndStr = @KeyAndStr + <br />'' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''<br />end<br />else if @xType = 106 or @xType = 108 -- int, decimal, numeric<br />begin<br />set @KeyAndStr = @KeyAndStr + <br />'' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''<br />end<br />else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real<br />begin <br />set @KeyAndStr = @KeyAndStr + <br />'' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''<br />end<br />else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit<br />begin<br />set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''<br />end<br />else if @xType = 36 -- 36 - id <br />begin<br />set @KeyAndStr = @KeyAndStr +<br />'' CASE WHEN d1.[''+@UKey+''] is null''+<br />'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+<br />'' ELSE d1.[''+@UKey+''] END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null''+<br />'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+<br />'' ELSE d2.[''+@UKey+''] END''<br />end<br />else if @xType = 61 or @xType = 58 -- datetime, smalldatetime<br />begin<br />set @KeyAndStr = @KeyAndStr +<br />'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+<br />''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''<br />end<br />else if @xType = 189 -- timestamp (189) <br />begin<br />set @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''<br />end<br />else if @xType = 98 -- SQL_variant<br />begin<br />set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''<br />end<br />set @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey <br />fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType<br />if @IndId &lt;&gt; @CurrIndId<br />begin<br />insert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)<br />set @CurrIndId = @IndId<br />set @CurrStatus = @IndStatus<br />set @KeyAndStr = ''''<br />set @KeyCommaStr = '''' <br />end<br />end <br />deallocate UKeys <br />insert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)<br />set @KeyCommaStr = null<br /><br />select @KeyCommaStr=i1.KeyCommaStr from #IndList1 i1<br />join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr<br />where (i1.IndStatus & 204<img src='/community/emoticons/emotion-11.gif' alt='8)' />&lt;&gt; 0 and (i2.IndStatus & 204<img src='/community/emoticons/emotion-11.gif' alt='8)' />&lt;&gt;0<br /><br />if @KeyCommaStr is null <br />set @KeyCommaStr = (select top 1 i1.KeyCommaStr from #IndList1 i1<br />join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr)<br />set @KeyAndStr = (select TOP 1 KeyAndStr from #IndList1 where KeyCommaStr = @KeyCommaStr)<br />if @KeyCommaStr is null<br />set @KeyCommaStr = ''''<br />if @KeyAndStr is null<br />set @KeyAndStr = '''''<br />set @SqlStrGetListOfColumns = '<br />set @AndStr = ''''<br />set @StrInd = 1<br />declare Cols cursor local fast_forward for <br />select c.name, c.xtype, c.length from '+@db1+'.dbo.sysobjects o, '+@db1+'.dbo.syscolumns c<br />where o.id = c.id and o.name = @Tab <br />and CHARINDEX(c.name, @KeyCommaStr) = 0<br />open Cols <br />fetch next from Cols into @Col, @xType, @len<br />while @@fetch_status = 0 <br />begin <br />if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar<br />begin<br />set @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''<br />end<br />if @xType = 173 or @xType = 165 -- binary, varbinary<br />begin<br />set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@Col+''] END=''+<br />''CASE WHEN d2.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@Col+''] END ''<br />end<br />else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney<br />begin<br />set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 971428763405345098745 ELSE d1.[''+@Col+''] END=''+<br />''CASE WHEN d2.[''+@Col+''] is null THEN 971428763405345098745 ELSE d2.[''+@Col+''] END ''<br />end<br />else if @xType = 106 or @xType = 108 -- int, decimal, numeric<br />begin<br />set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@Col+''] END=''+<br />''CASE WHEN d2.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@Col+''] END ''<br />end<br />else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real<br />begin <br />set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@Col+''] END=''+<br />''CASE WHEN d2.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@Col+''] END ''<br />end<br />else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit<br />begin<br />set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 99999 ELSE d1.[''+@Col+''] END=''+<br />''CASE WHEN d2.[''+@Col+''] is null THEN 99999 ELSE d2.[''+@Col+''] END ''<br />end<br />else if @xType = 36 -- 36 - id <br />begin<br />set @Eq = ''CASE WHEN d1.[''+@Col+''] is null''+<br />'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+<br />'' ELSE d1.[''+@Col+''] END=''+<br />''CASE WHEN d2.[''+@Col+''] is null''+<br />'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+<br />'' ELSE d2.[''+@Col+''] END''<br />end<br />else if @xType = 61 or @xType = 58 -- datetime, smalldatetime<br />begin<br />set @Eq =<br />''CASE WHEN d1.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@Col+''],109) END=''+<br />''CASE WHEN d2.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@Col+''],109) END ''<br />end<br />else if @xType = 34<br />begin<br />set @Eq = ''ISNULL(DATALENGTH(d1.[''+@Col+'']),0)=ISNULL(DATALENGTH(d2.[''+@Col+'']),0) '' <br />end<br />else if @xType = 35 or @xType = 99 -- text (35),ntext (99) <br />begin<br />set @Eq = ''ISNULL(SUBSTRING(d1.[''+@Col+''],1,DATALENGTH(d1.[''+@Col+<br />''])),''''!#null$'''')=ISNULL(SUBSTRING(d2.[''+@Col+''],1,DATALENGTH(d2.[''+@Col+''])),''''!#null$'''') ''<br />end<br />else if @xType = 189 <br />begin<br />if '+STR(@NoTimestamp)+' = 0 <br />set @Eq = ''d1.[''+@Col+'']=d2.[''+@Col+''] ''<br />else<br />set @Eq = ''1=1''<br />end<br />else if @xType = 98 -- SQL_variant<br />begin<br />set @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''<br />end<br />if @AndStr = ''''<br />set @AndStr = @AndStr + CHAR(10) + '' '' + @Eq <br />else<br />if len(@AndStr) + len('' and '' + @Eq)&lt;8000<br />set @AndStr = @AndStr + '' and '' + CHAR(10) + '' '' + @Eq <br />else<br />begin<br />set @StrInd = @StrInd + 1<br />Insert into ##CompareStr values(@StrInd,@AndStr)<br />set @AndStr = '' and '' + @Eq <br />end<br />fetch next from Cols into @Col, @xType, @len <br />end <br />deallocate Cols '<br />set @SqlStrCompareUKeyTables = '<br />if @KeyAndStr &lt;&gt; ''''<br />begin<br />set @SelectStr = ''SELECT ''+ @KeyCommaStr+'' INTO ##NotInDb2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 ''+ <br />'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 ''+ <br />'' WHERE ''+CHAR(10)+@KeyAndStr+'')''<br />if '+STR(@VerboseLevel)+' = 1<br />print CHAR(10)+''To find rows that are in '+@db1+', but are not in db2:''+CHAR(10)+<br />REPLACE (@SelectStr, ''into ##NotInDB2'','''')<br />exec (@SelectStr) <br />if @@rowcount &gt; 0 <br />set @NotInDB2 = 1 <br />set @SelectStr = ''SELECT ''+@KeyCommaStr+'' INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+ <br />'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ''+ <br />'' WHERE ''+CHAR(10)+@KeyAndStr+'')'' <br />if '+STR(@VerboseLevel)+' = 1<br />print CHAR(10)+''To find rows that are in '+@db2+', but are not in '+@db1+':''+CHAR(10)+<br />REPLACE (@SelectStr, ''into ##NotInDB1'','''')<br />exec (@SelectStr) <br />if @@rowcount &gt; 0 <br />set @NotInDB1 = 1 <br />-- if there are non-key columns<br />if @AndStr &lt;&gt; '''' <br />begin<br />set @PrintStr = '' Print ''<br />set @ExecStr = '' exec (''<br />set @SqlStr = ''''<br />Insert into ##CompareStr values(1,<br />''SELECT ''+ @KeyCommaStr+'' INTO ##NotEq FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+ <br />'' INNER JOIN '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ON ''+CHAR(10)+@KeyAndStr+CHAR(10)+''WHERE not('') <br />-- Adding last string in temp table containing a comparing string to execute<br />set @StrInd = @StrInd + 1<br />Insert into ##CompareStr values(@StrInd,@AndStr+'')'')<br />set @i = 1<br />while @i &lt;= @StrInd<br />begin<br />set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+<br />''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)<br />if @ExecStr &lt;&gt; '' exec (''<br />set @ExecStr = @ExecStr + ''+''<br />if @PrintStr &lt;&gt; '' Print ''<br />set @PrintStr = @PrintStr + ''+''<br />set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))<br />set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotEq'''','''''''') ''<br />set @i = @i + 1<br />end<br />set @ExecStr = @ExecStr + '') ''<br />set @ExecSqlStr = @SqlStr + @ExecStr <br />set @PrintSqlStr = @SqlStr + <br />'' Print CHAR(10)+''''To find rows that are different in non-key columns:'''' ''+<br />@PrintStr <br />if '+STR(@VerboseLevel)+' = 1<br />exec (@PrintSqlStr)<br />exec (@ExecSqlStr)<br /><br />if @@rowcount &gt; 0 <br />set @NotEq = 1 <br />end<br />else<br />if '+STR(@VerboseLevel)+' = 1<br />print CHAR(10)+''There are no non-key columns in the table''<br />truncate table ##CompareStr<br />if @NotInDB1 = 1 or @NotInDB2 = 1 or @NotEq = 1<br />begin <br />print CHAR(10)+''Data are different''<br />if @NotInDB2 = 1 and '+STR(@NumbToShow)+' &gt; 0<br />begin<br />print ''These key values exist in '+@db1+', but do not exist in '+@db2+': ''<br />set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''<br />exec (@SelectStr)<br />end<br />if @NotInDB1 = 1 and '+STR(@NumbToShow)+' &gt; 0<br />begin<br />print ''These key values exist in '+@db2+', but do not exist in '+@db1+': ''<br />set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''<br />exec (@SelectStr)<br />end<br />if @NotEq = 1 and '+STR(@NumbToShow)+' &gt; 0<br />begin<br />print ''Row(s) with these key values contain differences in non-key columns: ''<br />set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotEq''<br />exec (@SelectStr) <br />end<br />exec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'') <br />end <br />else<br />begin<br /><br />print CHAR(10)+''Data are identical''<br />exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'') <br />end<br />if exists (select * from tempdb.dbo.sysobjects where name like ''##NotEq%'')<br />drop table ##NotEq<br />end <br />else '<br />set @SqlStrCompareNonUKeyTables = '<br />begin<br />exec (''insert into #NoPKTables values(''''[''+@Tab+'']'''')'')<br />set @PrintStr = '' Print ''<br />set @ExecStr = '' exec (''<br />set @SqlStr = ''''<br />Insert into ##CompareStr values(1,<br />''SELECT ''+<br />'' * INTO ##NotInDB2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+<br />'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 WHERE '')<br />set @StrInd = @StrInd + 1<br />Insert into ##CompareStr values(@StrInd,@AndStr+'')'')<br />set @i = 1<br />while @i &lt;= @StrInd<br />begin<br />set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+<br />''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)<br />if @ExecStr &lt;&gt; '' exec (''<br />set @ExecStr = @ExecStr + ''+''<br />if @PrintStr &lt;&gt; '' Print ''<br />set @PrintStr = @PrintStr + ''+''<br />set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))<br />set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB2'''','''''''') ''<br />set @i = @i + 1<br />end<br />set @ExecStr = @ExecStr + '') ''<br />set @ExecSqlStr = @SqlStr + @ExecStr <br />set @PrintSqlStr = @SqlStr +<br />'' Print CHAR(10)+''''To find rows that are in '+@db1+', but are not in '+@db2+':'''' ''+<br />@PrintStr <br />if '+STR(@VerboseLevel)+' = 1<br />exec (@PrintSqlStr)<br />exec (@ExecSqlStr)<br /><br />if @@rowcount &gt; 0 <br />set @NotInDB2 = 1 <br />delete from ##CompareStr where ind = 1<br />set @PrintStr = '' Print ''<br />set @ExecStr = '' exec (''<br />set @SqlStr = ''''<br />Insert into ##CompareStr values(1,<br />''SELECT ''+<br />'' * INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+<br />'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 WHERE '')<br />set @i = 1<br />while @i &lt;= @StrInd<br />begin<br />set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+<br />''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)<br />if @ExecStr &lt;&gt; '' exec (''<br />set @ExecStr = @ExecStr + ''+''<br />if @PrintStr &lt;&gt; '' Print ''<br />set @PrintStr = @PrintStr + ''+''<br />set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))<br />set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB1'''','''''''') ''<br />set @i = @i + 1<br />end<br />set @ExecStr = @ExecStr + '') ''<br />set @ExecSqlStr = @SqlStr + @ExecStr <br />set @PrintSqlStr = @SqlStr +<br />'' Print CHAR(10)+''''To find rows that are in '+@db2+', but are not in '+@db1+':'''' ''+<br />@PrintStr <br />if '+STR(@VerboseLevel)+' = 1<br />exec (@PrintSqlStr)<br />exec (@ExecSqlStr)<br /><br />if @@rowcount &gt; 0 <br />set @NotInDB1 = 1 <br />truncate table ##CompareStr<br />if @NotInDB1 = 1 or @NotInDB2 = 1<br />begin <br />print CHAR(10)+''Data are different''<br />if @NotInDB2 = 1 and '+STR(@NumbToShow)+' &gt; 0<br />begin<br />print ''The row(s) exist in '+@db1+', but do not exist in '+@db2+': ''<br />set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''<br />exec (@SelectStr)<br />end<br />if @NotInDB1 = 1 and '+STR(@NumbToShow)+' &gt; 0<br />begin<br />print ''The row(s) exist in '+@db2+', but do not exist in '+@db1+': ''<br />set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''<br />exec (@SelectStr)<br />end<br />exec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'') <br />end <br />else<br />begin<br />print CHAR(10)+''Data are identical''<br />exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'') <br />end<br />end<br />if exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB1%'')<br />drop table ##NotInDB1<br />if exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB2%'')<br />drop table ##NotInDB2<br />NextTab:<br />fetch next from TabCur into @Tab, @d1User, @d2User <br />end <br />deallocate TabCur <br />'<br />exec (@SqlStrGetListOfKeys1+@SqlStrGetListOfKeys2+@SqlStrGetListOfColumns+<br />@SqlStrCompareUKeyTables+@SqlStrCompareNonUKeyTables)<br />print ' '<br />SET NOCOUNT OFF<br />if (select count(*) from #NoPKTables) &gt; 0<br />begin<br />select name as 'Table(s) without Unique key:' from #NoPKTables <br />end<br />if (select count(*) from #DiffTables) &gt; 0<br />begin<br />select name as 'Table(s) with the same name & structure, but different data:' from #DiffTables <br />end<br />else<br />print CHAR(10)+'No tables with the same name & structure, but different data'+CHAR(10)<br />if (select count(*) from #IdenticalTables) &gt; 0<br />begin<br />select name as 'Table(s) with the same name & structure and identical data:' from #IdenticalTables <br />end<br />if (select count(*) from #EmptyTables) &gt; 0<br />begin<br />select name as 'Table(s) with the same name & structure and empty in the both databases:' from #EmptyTables <br />end<br />drop table #TabToCheck<br />drop table ##CompareStr<br />drop table #DiffTables<br />drop table #IdenticalTables<br />drop table #EmptyTables<br />drop table #NoPKTables<br />drop table #IndList1<br />drop table #IndList2<br />return</pre><br /><br />Gaurav
  2. bradmcgehee New Member

  3. vbkenya New Member

    Great T-SQL but you can achieve better results faster using SQLDMO if a comparison really mattered.

    If you can lay your hands on it, please read:

    ACCESS-VB-SQL ADVISOR MAGAZINE
    Doc # 03696
    May 1999
    Length 6.75 pages
    On page 30 of the magazine



    Nathan H.O.
  4. preethi Member

    quote:
    This is from an article that is already published on this website at:
    http://www.sql-server-performance.com/vg_database_comparison_sp.asp

    After readingth article I got the script from SqlServer Central.com. IF i have other schemas and same table names are used by differnet schemas It will give an error


    How ever this is one of the great tool.

    G.R. Preethiviraj Kulasingham

Share This Page