sp_CompareDB

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

This is from an article that is already published on this website at:

http://www.sql-server-performance.com/vg_database_comparison_sp.asp

-----------------------------
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

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.

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