Monthly Archives: August 2012

Fixed Version of sp_CompareDB

Posted on by 
http://www.sql-server-performance.com/2001/database-comparison-sp/http://www.sql-server-performance.com/forum/threads/sp_comparedb.20521/USE MasterGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'dbp_CompareDB' and type = 'P')DROP PROC dbp_CompareDBGO---------------------------------------------------------------------------------------------- sp_CompareDB-- -- The SP compares structures and data in 2 databases.-- 1. Compares if all tables in one database have analog (by name) in second database-- Tables not existing in one of databases won't be used for data comparing-- 2. Compares if structures for tables with the same names are the same. Shows structural-- differences like:-- authors-- Column Phone: in db1 - char(12), in db2 - char(14)-- sales-- Column Location not in db2-- Tables, having different structures, won't be used for data comparing. However if the tables-- contain columns of the same type and different length (like Phone in the example above) or-- tables have compatible data types (have the same type in syscolumns - char and nchar, -- varchar and nvarchar etc) they will be allowed for data comparing.-- 3. Data comparison itself. -- 3.1 Get information about unique keys in the tables. If there are unique keys then one of them-- (PK is a highest priority candidate for this role) will be used to specify rows with-- different data.-- 3.2 Get information about all data columns in the table and form predicates that will be -- used to compare data.-- 3.3 Compare data with the criteria:-- a. if some unique keys from the table from first database do not exist in second db (only-- for tables with a unique key)-- b. if some unique keys from the table from second database do not exist in first db (only-- for tables with a unique key)-- c. if there are rows with the same values of unique keys and different data in other-- columns (only for tables with a unique key)-- d. if there are rows in the table from first database that don't have a twin in the -- table from second db-- e. if there are rows in the table from second database that don't have a twin in the -- table from first db---------------------------------------------------------------------------------------------- Parameters:-- 1. @db1 - name of first database to compare-- 2. @db2 - name of second database to compare-- 3. @TabList - list of tables to compare. if empty - all tables in the databases should be-- compared-- 4. @NumbToShow - number of rows with differences to show. Default - 10.-- 5. @OnlyStructure - flag, if set to 1, allows to avoid data comparing. Only structures should-- be compared. Default - 0-- 6. @NoTimestamp - flag, if set to 1, allows to avoid comparing of columns of timestamp-- data type. Default - 0-- 7. @VerboseLevel - if set to 1 allows to print querues used for data comparison---------------------------------------------------------------------------------------------- Created by Viktor Gorodnichenko (c)-- Created on: July 5, 2001--------------------------------------------------------------------------------------------CREATE PROC dbp_CompareDB@db1 varchar(128),@OnlyStructure bit = 0,@TabList varchar(8000) = '',@NumbToShow int = 10,@NoTimestamp bit = 0,@VerboseLevel tinyint = 0ASif @OnlyStructure <> 0set @OnlyStructure = 1if @NoTimestamp <> 0set @NoTimestamp = 1if @VerboseLevel <> 0set @VerboseLevel = 1SET NOCOUNT ONSET ANSI_WARNINGS ONSET ANSI_NULLS ONdeclare @sqlStr varchar(8000)set nocount on-- Checking if there are specified databasesdeclare @SrvName sysnamedeclare @DBName sysnameset @db1 = RTRIM(LTRIM(@db1))set @db2 = RTRIM(LTRIM(@db2))set @SrvName = @@SERVERNAMEif CHARINDEX('.',@db1) > 0beginset @SrvName = LEFT(@db1,CHARINDEX('.',@db1)-1)if not exists (select * from master.dbo.sysservers where srvname = @SrvName)beginprint 'There is no linked server named '+@SrvName+'. End of work.'return endset @DBName = RIGHT(@db1,LEN(@db1)-CHARINDEX('.',@db1))endelseset @DBName = @db1exec ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')if @@rowcount = 0beginprint 'There is no database named '+@db1+'. End of work.'return endset @SrvName = @@SERVERNAMEif CHARINDEX('.',@db2) > 0beginset @SrvName = LEFT(@db2,CHARINDEX('.',@db2)-1)if not exists (select * from master.dbo.sysservers where srvname = @SrvName)beginprint 'There is no linked server named '+@SrvName+'. End of work.'return endset @DBName = RIGHT(@db2,LEN(@db2)-CHARINDEX('.',@db2))endelseset @DBName = @db2exec ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')if @@rowcount = 0beginprint 'There is no database named '+@db2+'. End of work.'return endprint Replicate('-',LEN(@db1)+LEN(@db2)+25)print 'Comparing databases '+@db1+' and '+@db2print Replicate('-',LEN(@db1)+LEN(@db2)+25)print 'Options specified:'print ' Compare only structures: '+CASE WHEN @OnlyStructure = 0 THEN 'No' ELSE 'Yes' ENDprint ' List of tables to compare: '+CASE WHEN LEN(@TabList) = 0 THEN ' All tables' ELSE @TabList ENDprint ' Max number of different rows in each table to show: '+LTRIM(STR(@NumbToShow))print ' Compare timestamp columns: '+CASE WHEN @NoTimestamp = 0 THEN 'No' ELSE 'Yes' ENDprint ' Verbose level: '+CASE WHEN @VerboseLevel = 0 THEN 'Low' ELSE 'High' END------------------------------------------------------------------------------------------- Comparing structures-----------------------------------------------------------------------------------------print CHAR(10)+Replicate('-',36)print 'Comparing structure of the databases'print Replicate('-',36)if exists (select * from tempdb.dbo.sysobjects where name like '#TabToCheck%')drop table #TabToCheckcreate table #TabToCheck (name sysname)declare @NextCommaPos intif len(@TabList) > 0 beginwhile 1=1beginset @NextCommaPos = CHARINDEX(',',@TabList)if @NextCommaPos = 0beginset @sqlstr = 'insert into #TabToCheck values('''+@TabList+''')'exec (@sqlstr)breakendset @sqlstr = 'insert into #TabToCheck values('''+LEFT(@TabList,@NextCommaPos-1)+''')'exec (@sqlstr)set @TabList = RIGHT(@TabList,LEN(@TabList)-@NextCommaPos)endendelse -- then will check all tablesbeginexec ('insert into #TabToCheck select name from '+@db1+'.dbo.sysobjects where type = ''U''')exec ('insert into #TabToCheck select name from '+@db2+'.dbo.sysobjects where type = ''U''')end-- First check if at least one table specified in @TabList exists in db1exec ('declare @Name sysname select @Name=name from '+@db1+'.dbo.sysobjects where name in (select * from #TabToCheck)')if @@rowcount = 0beginprint 'No tables in '+@db1+' to check. End of work.'returnend-- Check if tables existing in db1 are in db2 (all tables or specified in @TabList)if exists (select * from tempdb.dbo.sysobjects where name like '#TabNotInDB2%')drop table #TabNotInDB2create table #TabNotInDB2 (name sysname)insert into #TabNotInDB2 exec ('select name from '+@db1+'.dbo.sysobjects d1o '+'where name in (select * from #TabToCheck) and '+' d1o.type = ''U'' and not exists '+'(select * from '+@db2+'.dbo.sysobjects d2o'+' where d2o.type = ''U'' and d2o.name = d1o.name)')if @@rowcount > 0beginprint CHAR(10)+'The table(s) exist in '+@db1+', but do not exist in '+@db2+':'select * from #TabNotInDB2 enddelete from #TabToCheck where name in (select * from #TabNotInDB2)drop table #TabNotInDB2if exists (select * from tempdb.dbo.sysobjects where name like '#TabNotInDB1%')drop table #TabNotInDB1create table #TabNotInDB1 (name sysname)insert into #TabNotInDB1 exec ('select name from '+@db2+'.dbo.sysobjects d1o '+'where name in (select * from #TabToCheck) and '+' d1o.type = ''U'' and not exists '+'(select * from '+@db1+'.dbo.sysobjects d2o'+' where d2o.type = ''U'' and d2o.name = d1o.name)')if @@rowcount > 0beginprint CHAR(10)+'The table(s) exist in '+@db2+', but do not exist in '+@db1+':'select * from #TabNotInDB1 enddelete from #TabToCheck where name in (select * from #TabNotInDB1)drop table #TabNotInDB1-- Comparing structures of tables existing in both dbsprint CHAR(10)+'Checking if there are tables existing in both databases having structural differences ...'+CHAR(10)if exists
 (select * from tempdb.dbo.sysobjects where name like '#DiffStructure%')drop table #DiffStructurecreate table #DiffStructure (name sysname)set @sqlStr='declare @TName1 sysname, @TName2 sysname, @CName1 sysname, @CName2 sysname,@TypeName1 sysname, @TypeName2 sysname,@CLen1 smallint, @CLen2 smallint, @Type1 sysname, @Type2 sysname, @PrevTName sysnamedeclare @DiffStructure bitdeclare Diff cursor fast_forward forselect d1o.name, d2o.name, d1c.name, d2c.name, d1t.name, d2t.name,d1c.length, d2c.length, d1c.type, d2c.typefrom ('+@db1+'.dbo.sysobjects d1o JOIN '+@db2+'.dbo.sysobjects d2o2 ON d1o.name = d2o2.name and d1o.type = ''U'' --only tables in both dbsand d1o.name in (select * from #TabToCheck)JOIN '+@db1+'.dbo.syscolumns d1c ON d1o.id = d1c.idJOIN '+@db1+'.dbo.systypes d1t ON d1c.xusertype = d1t.xusertype)FULL JOIN ('+@db2+'.dbo.sysobjects d2o JOIN '+@db1+'.dbo.sysobjects d1o2 ON d1o2.name = d2o.name and d2o.type = ''U'' --only tables in both dbsand d2o.name in (select * from #TabToCheck)JOIN '+@db2+'.dbo.syscolumns d2c ON d2c.id = d2o.idJOIN '+@db2+'.dbo.systypes d2t ON d2c.xusertype = d2t.xusertype)ON d1o.name = d2o.name and d1c.name = d2c.nameWHERE (not exists (select * from '+@db2+'.dbo.sysobjects d2o2JOIN '+@db2+'.dbo.syscolumns d2c2 ON d2o2.id = d2c2.idJOIN '+@db2+'.dbo.systypes d2t2 ON d2c2.xusertype = d2t2.xusertypewhere d2o2.type = ''U''and d2o2.name = d1o.name and d2c2.name = d1c.name and d2t2.name = d1t.nameand d2c2.length = d1c.length)OR not exists (select * from '+@db1+'.dbo.sysobjects d1o2JOIN '+@db1+'.dbo.syscolumns d1c2 ON d1o2.id = d1c2.idJOIN '+@db1+'.dbo.systypes d1t2 ON d1c2.xusertype = d1t2.xusertypewhere d1o2.type = ''U''and d1o2.name = d2o.name and d1c2.name = d2c.name and d1t2.name = d2t.nameand d1c2.length = d2c.length))order by coalesce(d1o.name,d2o.name), d1c.nameopen Difffetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,@CLen1, @CLen2, @Type1, @Type2set @PrevTName = ''''set @DiffStructure = 0while @@fetch_status = 0beginif Coalesce(@TName1,@TName2) <> @PrevTNamebeginif @PrevTName <> '''' and @DiffStructure = 1begininsert into #DiffStructure values (@PrevTName)set @DiffStructure = 0endset @PrevTName = Coalesce(@TName1,@TName2)print @PrevTNameendif @CName2 is nullprint '' Colimn ''+RTRIM(@CName1)+'' not in '+@db2+'''elseif @CName1 is nullprint '' Colimn ''+RTRIM(@CName2)+'' not in '+@db1+'''elseif @TypeName1 <> @TypeName2print '' Colimn ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+'', in '+@db2+' - ''+RTRIM(@TypeName2)else --the columns are not null(are in both dbs) and types are equal,then length are diffprint '' Colimn ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+''(''+LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen1/2 else @CLen1 end))+''), in '+@db2+' - ''+RTRIM(@TypeName2)+''(''+LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen2/2 else @CLen2 end))+'')''if @Type1 = @Type2set @DiffStructure=@DiffStructure -- Do nothing. Cannot invert predicateelseset @DiffStructure = 1fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,@CLen1, @CLen2, @Type1, @Type2enddeallocate Diffif @DiffStructure = 1insert into #DiffStructure values (@PrevTName)'exec (@sqlStr)if (select count(*) from #DiffStructure) > 0beginprint CHAR(10)+'The table(s) have the same name and different structure in the databases:'select distinct * from #DiffStructure delete from #TabToCheck where name in (select * from #DiffStructure)endelseprint CHAR(10)+'There are no tables with the same name and structural differences in the databases'+CHAR(10)+CHAR(10)if @OnlyStructure = 1beginprint 'The option ''Only compare structures'' was specified. End of work.'returnendexec ('declare @Name sysname select @Name=d1o.namefrom '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''and d1o.name not in (''dtproperties'') and d1o.name in (select * from #TabToCheck)')if @@rowcount = 0beginprint 'There are no tables with the same name and structure in the databases to compare. End of work.'returnend------------------------------------------------------------------------------------------- Comparing data ------------------------------------------------------------------------------------------- ##CompareStr - will be used to pass comparing strings into dynamic script-- to execute the stringif exists (select * from tempdb.dbo.sysobjects where name like '##CompareStr%')drop table ##CompareStrcreate table ##CompareStr (Ind int, CompareStr varchar(8000))if exists (select * from tempdb.dbo.sysobjects where name like '#DiffTables%')drop table #DiffTablescreate table #DiffTables (Name sysname)if exists (select * from tempdb.dbo.sysobjects where name like '#IdenticalTables%')drop table #IdenticalTablescreate table #IdenticalTables (Name sysname)if exists (select * from tempdb.dbo.sysobjects where name like '#EmptyTables%')drop table #EmptyTablescreate table #EmptyTables (Name sysname)if exists (select * from tempdb.dbo.sysobjects where name like '#NoPKTables%')drop table #NoPKTablescreate table #NoPKTables (Name sysname)if exists (select * from tempdb.dbo.sysobjects where name like '#IndList1%')truncate table #IndList1else create table #IndList1 (IndId int, IndStatus int,KeyAndStr varchar(7000), KeyCommaStr varchar(1000))if exists (select * from tempdb.dbo.sysobjects where name like '#IndList2%')truncate table #IndList2elsecreate table #IndList2 (IndId smallint, IndStatus int,KeyAndStr varchar(7000), KeyCommaStr varchar(1000))print Replicate('-',51)print 'Comparing data in tables with indentical structure:'print Replicate('-',51)---------------------------------------------------------------------------------------------- Cursor for all tables in dbs (or for all specified tables if parameter @TabList is passed)--------------------------------------------------------------------------------------------declare @SqlStrGetListOfKeys1 varchar(8000)declare @SqlStrGetListOfKeys2 varchar(8000)declare @SqlStrGetListOfColumns varchar(8000)declare @SqlStrCompareUKeyTables varchar(8000)declare @SqlStrCompareNonUKeyTables varchar(8000)set @SqlStrGetListOfKeys1 = 'declare @sqlStr varchar(8000)declare @ExecSqlStr varchar(8000)declare @PrintSqlStr varchar(8000)declare @Tab varchar(128)declare @d1User varchar(12 8)declare @d2User varchar(128)declare @KeyAndStr varchar(8000) declare @KeyCommaStr varchar(8000) declare @AndStr varchar(8000) declare @Eq varchar(8000) declare @IndId intdeclare @IndStatus intdeclare @CurrIndId smallintdeclare @CurrStatus intdeclare @UKey sysname declare @Col varchar(128)declare @LastUsedCol varchar(128)declare @xType intdeclare @Len intdeclare @SelectStr varchar(8000) declare @ExecSql nvarchar(1000) declare @NotInDB1 bit declare @NotInDB2 bit declare @NotEq bit declare @Numb intdeclare @Cnt1 intdeclare @Cnt2 intset @Numb = 0declare @StrInd intdeclare @i intdeclare @PrintStr varchar(8000)declare @ExecStr varchar(8000)declare TabCur cursor for select d1o.name, d1u.name, d2u.name from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o,'+@db1+'.dbo.sysusers d1u, '+@db2+'.dbo.sysusers d2u where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''and d1o.uid = d1u.uid and d2o.uid = d2u.uid and d1o.name not in (''dtproperties'') and d1o.name in (select * from #TabToCheck)order by 1open TabCur fetch next from TabCur into @Tab, @d1User, @d2User while @@fetch_status = 0 begin set @Numb = @Numb + 1print Char(13)+Char(10)+LTRIM(STR(@Numb))+''. TABLE: [''+@Tab+''] ''set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db1+'.[''+@d1User+''].[''+@Tab+'']''exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt1 outputprint CHAR(10)+STR(@Cnt1)+'' rows in '+@db1+'''set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db2+'.[''+@d2User+''].[''+@Tab+'']''exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt2 outputprint STR(@Cnt2)+'' rows in '+@db2+'''if @Cnt1 = 0 and @Cnt2 = 0beginexec ('' insert into #EmptyTables values(''''[''+@Tab+'']'''')'') goto NextTabendset @KeyAndStr = '''' set @KeyCommaStr = '''' set @NotInDB1 = 0s
et @NotInDB2 = 0 set @NotEq = 0set @KeyAndStr = '''' set @KeyCommaStr = '''' truncate table #IndList1declare UKeys cursor fast_forward for 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 where i.id = o.id and o.name = @Taband (i.status & 2)<>0 and k.id = o.id and k.indid = i.indid and c.id = o.id and c.colid = k.colid order by i.indid, c.nameopen UKeys fetch next from UKeys into @IndId, @IndStatus, @UKey, @xTypeset @CurrIndId = @IndIdset @CurrStatus = @IndStatuswhile @@fetch_status = 0 begin if @KeyAndStr <> ''''begin set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10) set @KeyCommaStr = @KeyCommaStr + '', '' end if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarcharbeginset @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''endif @xType = 173 or @xType = 165 -- binary, varbinarybeginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoneybeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 106 or @xType = 108 -- int, decimal, numericbeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 62 or @xType = 59 -- 62 - float, 59 - realbegin set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bitbeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 36 -- 36 - id beginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d2.[''+@UKey+''] END''endelse if @xType = 61 or @xType = 58 -- datetime, smalldatetimebeginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''endelse if @xType = 189 -- timestamp (189) beginset @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''endelse if @xType = 98 -- SQL_variantbeginset @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''endset @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey fetch next from UKeys into @IndId, @IndStatus, @UKey, @xTypeif @IndId <> @CurrIndIdbegininsert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)set @CurrIndId = @IndIdset @CurrStatus = @IndStatusset @KeyAndStr = ''''set @KeyCommaStr = '''' endend deallocate UKeys insert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)'set @SqlStrGetListOfKeys2 = 'set @KeyAndStr = '''' set @KeyCommaStr = '''' truncate table #IndList2declare UKeys cursor fast_forward for 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 where i.id = o.id and o.name = @Taband (i.status & 2)<>0 and k.id = o.id and k.indid = i.indid and c.id = o.id and c.colid = k.colid order by i.indid, c.nameopen UKeys fetch next from UKeys into @IndId, @IndStatus, @UKey, @xTypeset @CurrIndId = @IndIdset @CurrStatus = @IndStatuswhile @@fetch_status = 0 begin if @KeyAndStr <> ''''begin set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10) set @KeyCommaStr = @KeyCommaStr + '', '' end if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarcharbeginset @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''endif @xType = 173 or @xType = 165 -- binary, varbinarybeginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoneybeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 106 or @xType = 108 -- int, decimal, numericbeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 62 or @xType = 59 -- 62 - float, 59 - realbegin set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bitbeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 36 -- 36 - id beginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d2.[''+@UKey+''] END''endelse if @xType = 61 or @xType = 58 -- datetime, smalldatetimebeginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''endelse if @xType = 189 -- timestamp (189) beginset @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''endelse if @xType = 98 -- SQL_variantbeginset @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''endset @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey fetch next from UKeys into @IndId, @IndStatus, @UKey, @xTypeif @IndId <> @CurrIndIdbegininsert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)set @CurrIndId = @IndIdset @CurrStatus = @IndStatusset @KeyAndStr = ''''set @KeyCommaStr = '''' endend deallocate UKeys insert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)set @KeyCommaStr = nullselect @KeyCommaStr=i1.KeyCommaStr from #IndList1 i1join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStrwhere (i1.IndStatus & 2048)<> 0 and (i2.IndStatus & 2048)<>0if @KeyCommaStr is null set @KeyCommaStr = (select top 1 i1.KeyCommaStr from #IndList1 i1join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr)set @KeyAndStr = (select TOP 1 KeyAndStr from #IndList1 where KeyCommaStr = @KeyCommaStr)if @KeyCommaStr is nullset @KeyCommaStr = ''''if @KeyAndSt
r is nullset @KeyAndStr = '''''set @SqlStrGetListOfColumns = 'set @AndStr = ''''set @StrInd = 1declare Cols cursor local fast_forward for select c.name, c.xtype, c.length from '+@db1+'.dbo.sysobjects o, '+@db1+'.dbo.syscolumns cwhere o.id = c.id and o.name = @Tab and CHARINDEX(c.name, @KeyCommaStr) = 0open Cols fetch next from Cols into @Col, @xType, @lenwhile @@fetch_status = 0 begin if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarcharbeginset @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''endif @xType = 173 or @xType = 165 -- binary, varbinarybeginset @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@Col+''] END ''endelse if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoneybeginset @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 971428763405345098745 ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null THEN 971428763405345098745 ELSE d2.[''+@Col+''] END ''endelse if @xType = 106 or @xType = 108 -- int, decimal, numericbeginset @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@Col+''] END ''endelse if @xType = 62 or @xType = 59 -- 62 - float, 59 - realbegin set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@Col+''] END ''endelse if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bitbeginset @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 99999 ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null THEN 99999 ELSE d2.[''+@Col+''] END ''endelse if @xType = 36 -- 36 - id beginset @Eq = ''CASE WHEN d1.[''+@Col+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d2.[''+@Col+''] END''endelse if @xType = 61 or @xType = 58 -- datetime, smalldatetimebeginset @Eq =''CASE WHEN d1.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@Col+''],109) END=''+''CASE WHEN d2.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@Col+''],109) END ''endelse if @xType = 34beginset @Eq = ''ISNULL(DATALENGTH(d1.[''+@Col+'']),0)=ISNULL(DATALENGTH(d2.[''+@Col+'']),0) '' endelse if @xType = 35 or @xType = 99 -- text (35),ntext (99) beginset @Eq = ''ISNULL(SUBSTRING(d1.[''+@Col+''],1,DATALENGTH(d1.[''+@Col+''])),''''!#null$'''')=ISNULL(SUBSTRING(d2.[''+@Col+''],1,DATALENGTH(d2.[''+@Col+''])),''''!#null$'''') ''endelse if @xType = 189 beginif '+STR(@NoTimestamp)+' = 0 set @Eq = ''d1.[''+@Col+'']=d2.[''+@Col+''] ''elseset @Eq = ''1=1''endelse if @xType = 98 -- SQL_variantbeginset @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''endif @AndStr = ''''set @AndStr = @AndStr + CHAR(10) + '' '' + @Eq elseif len(@AndStr) + len('' and '' + @Eq)<8000set @AndStr = @AndStr + '' and '' + CHAR(10) + '' '' + @Eq elsebeginset @StrInd = @StrInd + 1Insert into ##CompareStr values(@StrInd,@AndStr)set @AndStr = '' and '' + @Eq endfetch next from Cols into @Col, @xType, @len end deallocate Cols 'set @SqlStrCompareUKeyTables = 'if @KeyAndStr <> ''''beginset @SelectStr = ''SELECT ''+ @KeyCommaStr+'' INTO ##NotInDb2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 ''+ '' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 ''+ '' WHERE ''+CHAR(10)+@KeyAndStr+'')''if '+STR(@VerboseLevel)+' = 1print CHAR(10)+''To find rows that are in '+@db1+', but are not in db2:''+CHAR(10)+REPLACE (@SelectStr, ''into ##NotInDB2'','''')exec (@SelectStr) if @@rowcount > 0 set @NotInDB2 = 1 set @SelectStr = ''SELECT ''+@KeyCommaStr+'' INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+ '' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ''+ '' WHERE ''+CHAR(10)+@KeyAndStr+'')'' if '+STR(@VerboseLevel)+' = 1print CHAR(10)+''To find rows that are in '+@db2+', but are not in '+@db1+':''+CHAR(10)+REPLACE (@SelectStr, ''into ##NotInDB1'','''')exec (@SelectStr) if @@rowcount > 0 set @NotInDB1 = 1 -- if there are non-key columnsif @AndStr <> '''' beginset @PrintStr = '' Print ''set @ExecStr = '' exec (''set @SqlStr = ''''Insert into ##CompareStr values(1,''SELECT ''+ @KeyCommaStr+'' INTO ##NotEq FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+ '' INNER JOIN '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ON ''+CHAR(10)+@KeyAndStr+CHAR(10)+''WHERE not('') -- Adding last string in temp table containing a comparing string to executeset @StrInd = @StrInd + 1Insert into ##CompareStr values(@StrInd,@AndStr+'')'')set @i = 1while @i <= @StrIndbeginset @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)if @ExecStr <> '' exec (''set @ExecStr = @ExecStr + ''+''if @PrintStr <> '' Print ''set @PrintStr = @PrintStr + ''+''set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotEq'''','''''''') ''set @i = @i + 1endset @ExecStr = @ExecStr + '') ''set @ExecSqlStr = @SqlStr + @ExecStr set @PrintSqlStr = @SqlStr + '' Print CHAR(10)+''''To find rows that are different in non-key columns:'''' ''+@PrintStr if '+STR(@VerboseLevel)+' = 1exec (@PrintSqlStr)exec (@ExecSqlStr)if @@rowcount > 0 set @NotEq = 1 endelseif '+STR(@VerboseLevel)+' = 1print CHAR(10)+''There are no non-key columns in the table''truncate table ##CompareStrif @NotInDB1 = 1 or @NotInDB2 = 1 or @NotEq = 1begin print CHAR(10)+''Data are different''if @NotInDB2 = 1 and '+STR(@NumbToShow)+' > 0beginprint ''These key values exist in '+@db1+', but do not exist in '+@db2+': ''set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''exec (@SelectStr)endif @NotInDB1 = 1 and '+STR(@NumbToShow)+' > 0beginprint ''These key values exist in '+@db2+', but do not exist in '+@db1+': ''set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''exec (@SelectStr)endif @NotEq = 1 and '+STR(@NumbToShow)+' > 0beginprint ''Row(s) with these key values contain differences in non-key columns: ''set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotEq''exec (@SelectStr) endexec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'') end elsebeginprint CHAR(10)+''Data are identical''exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'') endif exists (select * from tempdb.dbo.sysobjects where name like ''##NotEq%'')drop table ##NotEqend else 'set @SqlStrCompareNonUKeyTables = 'beginexec (''insert into #NoPKTables values(''''[''+@Tab+'']'''')'')set @PrintStr = '' Print ''set @ExecStr = '' exec (''set @SqlStr = ''''Insert into ##CompareStr values(1,''SELECT ''+'' * INTO ##NotInDB2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 WHERE '')set @StrInd = @StrInd + 1Insert into ##CompareStr values(@StrInd,@AndStr+'')'')set @i = 1while @i <= @StrIndbeginset @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)if @ExecStr <> '' exec (''set @ExecStr = @ExecStr + ''+''if @PrintStr <> '' Print ''set @PrintStr = @PrintStr + ''+''set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB2'''','''''''') ''set @i = @i + 1endset @ExecStr = @ExecStr + '') ''set @ExecSqlStr = @SqlStr + @ExecStr set @PrintSqlStr = @SqlStr +'' Print CHAR(10)+''''To find rows that are in '+@db1+', but are not in '+@db2+':'
''' ''+@PrintStr if '+STR(@VerboseLevel)+' = 1exec (@PrintSqlStr)exec (@ExecSqlStr)if @@rowcount > 0 set @NotInDB2 = 1 delete from ##CompareStr where ind = 1set @PrintStr = '' Print ''set @ExecStr = '' exec (''set @SqlStr = ''''Insert into ##CompareStr values(1,''SELECT ''+'' * INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 WHERE '')set @i = 1while @i <= @StrIndbeginset @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)if @ExecStr <> '' exec (''set @ExecStr = @ExecStr + ''+''if @PrintStr <> '' Print ''set @PrintStr = @PrintStr + ''+''set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB1'''','''''''') ''set @i = @i + 1endset @ExecStr = @ExecStr + '') ''set @ExecSqlStr = @SqlStr + @ExecStr set @PrintSqlStr = @SqlStr +'' Print CHAR(10)+''''To find rows that are in '+@db2+', but are not in '+@db1+':'''' ''+@PrintStr if '+STR(@VerboseLevel)+' = 1exec (@PrintSqlStr)exec (@ExecSqlStr)if @@rowcount > 0 set @NotInDB1 = 1 truncate table ##CompareStrif @NotInDB1 = 1 or @NotInDB2 = 1begin print CHAR(10)+''Data are different''if @NotInDB2 = 1 and '+STR(@NumbToShow)+' > 0beginprint ''The row(s) exist in '+@db1+', but do not exist in '+@db2+': ''set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''exec (@SelectStr)endif @NotInDB1 = 1 and '+STR(@NumbToShow)+' > 0beginprint ''The row(s) exist in '+@db2+', but do not exist in '+@db1+': ''set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''exec (@SelectStr)endexec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'') end elsebeginprint CHAR(10)+''Data are identical''exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'') endendif exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB1%'')drop table ##NotInDB1if exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB2%'')drop table ##NotInDB2NextTab:fetch next from TabCur into @Tab, @d1User, @d2User end deallocate TabCur 'exec (@SqlStrGetListOfKeys1+@SqlStrGetListOfKeys2+@SqlStrGetListOfColumns+@SqlStrCompareUKeyTables+@SqlStrCompareNonUKeyTables)print ' 'SET NOCOUNT OFFif (select count(*) from #NoPKTables) > 0beginselect name as 'Table(s) without Unique key:' from #NoPKTables endif (select count(*) from #DiffTables) > 0beginselect name as 'Table(s) with the same name & structure, but different data:' from #DiffTables endelseprint CHAR(10)+'No tables with the same name & structure, but different data'+CHAR(10)if (select count(*) from #IdenticalTables) > 0beginselect name as 'Table(s) with the same name & structure and identical data:' from #IdenticalTables endif (select count(*) from #EmptyTables) > 0beginselect name as 'Table(s) with the same name & structure and empty in the both databases:' from #EmptyTables enddrop table #TabToCheckdrop table ##CompareStrdrop table #DiffTablesdrop table #IdenticalTablesdrop table #EmptyTablesdrop table #NoPKTablesdrop table #IndList1drop table #IndList2return

sp_CompareDB

Posted on by

http://www.sql-server-performance.com/2001/database-comparison-sp/ http://www.sql-server-performance.com/forum/threads/sp_comparedb.20521

How to Run sp_CompareDB Stored Procedure

To make a long story short, it was quite a challenge not just to create a script comparing databases, but also to make it free of excessive restrictions, fast, and easy-to-run. All you need to install the sp_CompareDB stored procedure is to execute the script against the Master database. Then set the Query Analyzer option “Results in Text”) to get the result sets and comments on the same page).

Once the stored procedure has been created, simply run:

sp_CompareDB ‘<dbName1>’,’<dbName2>’

And enjoy seeing the differences.

If you are comparing a database from a linked server, then the command would be:

sp_CompareDB ‘<LinkedServer1>.<dbName1>’,’<dbName2>’

You can use the following parameters for the SP, besides the obvious db1 name (parameter name @db1) and db2 name (parameter name @db2).

@TabList – comma separated list of tables to compare. If empty – all tables in the databases should be compared

@NumbToShow – number of rows with differences to show. Default – 10. Be cautious increasing it. For tables with a unique key just the keys of rows with differences should be shown. But for tables without a unique key, all data for rows with differences are shown. After all it’s unlikely that somebody needs a boundless report to get lost in the ocean of digits and letters. If databases are completely different then there is no sense comparing them.

@OnlyStructure – flag being set to 1 allows you to avoid data comparing. Only structures should be compared. Default 0.

@NoTimestamp – flag being set to 1 allows you to avoid comparing of columns with timestamp data type. Default 0.

@VerboseLevel – flag being set to 1 lets you see queries used for data comparison. They can be used easily to synchronize data in the databases if you need it. Default 0.

A final note: This SP is absolutely safe. It only gets data from databases while not making any changes. The databases can even be in read-only mode. However, if you have big tables without a unique key, leave enough free disk space before running the SP. The space available must be at least twice bigger then the space consumed by the largest table in the databases.

---------------------------------------------------------------------------------------------------------------------------->USE MasterGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'dbp_CompareDB' and type = 'P')DROP PROC dbp_CompareDBGO---------------------------------------------------------------------------------------------- sp_CompareDB-- -- The SP compares structures and data in 2 databases.-- 1. Compares if all tables in one database have analog (by name) in second database-- Tables not existing in one of databases won't be used for data comparing-- 2. Compares if structures for tables with the same names are the same. Shows structural-- differences like:-- authors-- Column Phone: in db1 - char(12), in db2 - char(14)-- sales-- Column Location not in db2-- Tables, having different structures, won't be used for data comparing. However if the tables-- contain columns of the same type and different length (like Phone in the example above) or-- tables have compatible data types (have the same type in syscolumns - char and nchar, -- varchar and nvarchar etc) they will be allowed for data comparing.-- 3. Data comparison itself. -- 3.1 Get information about unique keys in the tables. If there are unique keys then one of them-- (PK is a highest priority candidate for this role) will be used to specify rows with-- different data.-- 3.2 Get information about all data columns in the table and form predicates that will be -- used to compare data.-- 3.3 Compare data with the criteria:-- a. if some unique keys from the table from first database do not exist in second db (only-- for tables with a unique key)-- b. if some unique keys from the table from second database do not exist in first db (only-- for tables with a unique key)-- c. if there are rows with the same values of unique keys and different data in other-- columns (only for tables with a unique key)-- d. if there are rows in the table from first database that don't have a twin in the -- table from second db-- e. if there are rows in the table from second database that don't have a twin in the -- table from first db---------------------------------------------------------------------------------------------- Parameters:-- 1. @db1 - name of first database to compare-- 2. @db2 - name of second database to compare-- 3. @TabList - list of tables to compare. if empty - all tables in the databases should be-- compared-- 4. @NumbToShow - number of rows with differences to show. Default - 10.-- 5. @OnlyStructure - flag, if set to 1, allows to avoid data comparing. Only structures should-- be compared. Default - 0-- 6. @NoTimestamp - flag, if set to 1, allows to avoid comparing of columns of timestamp-- data type. Default - 0-- 7. @VerboseLevel - if set to 1 allows to print querues used for data comparison---------------------------------------------------------------------------------------------- Created by Viktor Gorodnichenko (c)-- Created on: July 5, 2001--------------------------------------------------------------------------------------------CREATE PROC dbp_CompareDB@db1 varchar(12,@db2 varchar(12,@OnlyStructure bit = 0,@TabList varchar(8000) = '',@NumbToShow int = 10,@NoTimestamp bit = 0,@VerboseLevel tinyint = 0ASif @OnlyStructure <> 0set @OnlyStructure = 1if @NoTimestamp <> 0set @NoTimestamp = 1if @VerboseLevel <> 0set @VerboseLevel = 1SET NOCOUNT ONSET ANSI_WARNINGS ONSET ANSI_NULLS ONdeclare @sqlStr varchar(8000)set nocount on-- Checking if there are specified databasesdeclare @SrvName sysnamedeclare @DBName sysnameset @db1 = RTRIM(LTRIM(@db1))set @db2 = RTRIM(LTRIM(@db2))set @SrvName = @@SERVERNAMEif CHARINDEX('.',@db1) > 0beginset @SrvName = LEFT(@db1,CHARINDEX('.',@db1)-1)if not exists (select * from master.dbo.sysservers where srvname = @SrvName)beginprint 'There is no linked server named '+@SrvName+'. End of work.'return endset @DBName = RIGHT(@db1,LEN(@db1)-CHARINDEX('.',@db1))endelseset @DBName = @db1exec ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')if @@rowcount = 0beginprint 'There is no database named '+@db1+'. End of work.'return endset @SrvName = @@SERVERNAMEif CHARINDEX('.',@db2) > 0beginset @SrvName = LEFT(@db2,CHARINDEX('.',@db2)-1)if not exists (select * from master.dbo.sysservers where srvname = @SrvName)beginprint 'There is no linked server named '+@SrvName+'. End of work.'return endset @DBName = RIGHT(@db2,LEN(@db2)-CHARINDEX('.',@db2))endelseset @DBName = @db2exec ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')if @@rowcount = 0beginprint
'There is no database named '+@db2+'. End of work.'return endprint Replicate('-',LEN(@db1)+LEN(@db2)+25)print 'Comparing databases '+@db1+' and '+@db2print Replicate('-',LEN(@db1)+LEN(@db2)+25)print 'Options specified:'print ' Compare only structures: '+CASE WHEN @OnlyStructure = 0 THEN 'No' ELSE 'Yes' ENDprint ' List of tables to compare: '+CASE WHEN LEN(@TabList) = 0 THEN ' All tables' ELSE @TabList ENDprint ' Max number of different rows in each table to show: '+LTRIM(STR(@NumbToShow))print ' Compare timestamp columns: '+CASE WHEN @NoTimestamp = 0 THEN 'No' ELSE 'Yes' ENDprint ' Verbose level: '+CASE WHEN @VerboseLevel = 0 THEN 'Low' ELSE 'High' END------------------------------------------------------------------------------------------- Comparing structures-----------------------------------------------------------------------------------------print CHAR(10)+Replicate('-',36)print 'Comparing structure of the databases'print Replicate('-',36)if exists (select * from tempdb.dbo.sysobjects where name like '#TabToCheck%')drop table #TabToCheckcreate table #TabToCheck (name sysname)declare @NextCommaPos intif len(@TabList) > 0 beginwhile 1=1beginset @NextCommaPos = CHARINDEX(',',@TabList)if @NextCommaPos = 0beginset @sqlstr = 'insert into #TabToCheck values('''+@TabList+''')'exec (@sqlstr)breakendset @sqlstr = 'insert into #TabToCheck values('''+LEFT(@TabList,@NextCommaPos-1)+''')'exec (@sqlstr)set @TabList = RIGHT(@TabList,LEN(@TabList)-@NextCommaPos)endendelse -- then will check all tablesbeginexec ('insert into #TabToCheck select name from '+@db1+'.dbo.sysobjects where type = ''U''')exec ('insert into #TabToCheck select name from '+@db2+'.dbo.sysobjects where type = ''U''')end-- First check if at least one table specified in @TabList exists in db1exec ('declare @Name sysname select @Name=name from '+@db1+'.dbo.sysobjects where name in (select * from #TabToCheck)')if @@rowcount = 0beginprint 'No tables in '+@db1+' to check. End of work.'returnend-- Check if tables existing in db1 are in db2 (all tables or specified in @TabList)if exists (select * from tempdb.dbo.sysobjects where name like '#TabNotInDB2%')drop table #TabNotInDB2create table #TabNotInDB2 (name sysname)insert into #TabNotInDB2 exec ('select name from '+@db1+'.dbo.sysobjects d1o '+'where name in (select * from #TabToCheck) and '+' d1o.type = ''U'' and not exists '+'(select * from '+@db2+'.dbo.sysobjects d2o'+' where d2o.type = ''U'' and d2o.name = d1o.name)')if @@rowcount > 0beginprint CHAR(10)+'The table(s) exist in '+@db1+', but do not exist in '+@db2+':'select * from #TabNotInDB2 enddelete from #TabToCheck where name in (select * from #TabNotInDB2)drop table #TabNotInDB2if exists (select * from tempdb.dbo.sysobjects where name like '#TabNotInDB1%')drop table #TabNotInDB1create table #TabNotInDB1 (name sysname)insert into #TabNotInDB1 exec ('select name from '+@db2+'.dbo.sysobjects d1o '+'where name in (select * from #TabToCheck) and '+' d1o.type = ''U'' and not exists '+'(select * from '+@db1+'.dbo.sysobjects d2o'+' where d2o.type = ''U'' and d2o.name = d1o.name)')if @@rowcount > 0beginprint CHAR(10)+'The table(s) exist in '+@db2+', but do not exist in '+@db1+':'select * from #TabNotInDB1 enddelete from #TabToCheck where name in (select * from #TabNotInDB1)drop table #TabNotInDB1-- Comparing structures of tables existing in both dbsprint CHAR(10)+'Checking if there are tables existing in both databases having structural differences ...'+CHAR(10)if exists (select * from tempdb.dbo.sysobjects where name like '#DiffStructure%')drop table #DiffStructurecreate table #DiffStructure (name sysname)set @sqlStr='declare @TName1 sysname, @TName2 sysname, @CName1 sysname, @CName2 sysname,@TypeName1 sysname, @TypeName2 sysname,@CLen1 smallint, @CLen2 smallint, @Type1 sysname, @Type2 sysname, @PrevTName sysnamedeclare @DiffStructure bitdeclare Diff cursor fast_forward forselect d1o.name, d2o.name, d1c.name, d2c.name, d1t.name, d2t.name,d1c.length, d2c.length, d1c.type, d2c.typefrom ('+@db1+'.dbo.sysobjects d1o JOIN '+@db2+'.dbo.sysobjects d2o2 ON d1o.name = d2o2.name and d1o.type = ''U'' --only tables in both dbsand d1o.name in (select * from #TabToCheck)JOIN '+@db1+'.dbo.syscolumns d1c ON d1o.id = d1c.idJOIN '+@db1+'.dbo.systypes d1t ON d1c.xusertype = d1t.xusertype)FULL JOIN ('+@db2+'.dbo.sysobjects d2o JOIN '+@db1+'.dbo.sysobjects d1o2 ON d1o2.name = d2o.name and d2o.type = ''U'' --only tables in both dbsand d2o.name in (select * from #TabToCheck)JOIN '+@db2+'.dbo.syscolumns d2c ON d2c.id = d2o.idJOIN '+@db2+'.dbo.systypes d2t ON d2c.xusertype = d2t.xusertype)ON d1o.name = d2o.name and d1c.name = d2c.nameWHERE (not exists (select * from '+@db2+'.dbo.sysobjects d2o2JOIN '+@db2+'.dbo.syscolumns d2c2 ON d2o2.id = d2c2.idJOIN '+@db2+'.dbo.systypes d2t2 ON d2c2.xusertype = d2t2.xusertypewhere d2o2.type = ''U''and d2o2.name = d1o.name and d2c2.name = d1c.name and d2t2.name = d1t.nameand d2c2.length = d1c.length)OR not exists (select * from '+@db1+'.dbo.sysobjects d1o2JOIN '+@db1+'.dbo.syscolumns d1c2 ON d1o2.id = d1c2.idJOIN '+@db1+'.dbo.systypes d1t2 ON d1c2.xusertype = d1t2.xusertypewhere d1o2.type = ''U''and d1o2.name = d2o.name and d1c2.name = d2c.name and d1t2.name = d2t.nameand d1c2.length = d2c.length))order by coalesce(d1o.name,d2o.name), d1c.nameopen Difffetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,@CLen1, @CLen2, @Type1, @Type2set @PrevTName = ''''set @DiffStructure = 0while @@fetch_status = 0beginif Coalesce(@TName1,@TName2) <> @PrevTNamebeginif @PrevTName <> '''' and @DiffStructure = 1begininsert into #DiffStructure values (@PrevTName)set @DiffStructure = 0endset @PrevTName = Coalesce(@TName1,@TName2)print @PrevTNameendif @CName2 is nullprint '' Colimn ''+RTRIM(@CName1)+'' not in '+@db2+'''elseif @CName1 is nullprint '' Colimn ''+RTRIM(@CName2)+'' not in '+@db1+'''elseif @TypeName1 <> @TypeName2print '' Colimn ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+'', in '+@db2+' - ''+RTRIM(@TypeName2)else --the columns are not null(are in both dbs) and types are equal,then length are diffprint '' Colimn ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+''(''+LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen1/2 else @CLen1 end))+''), in '+@db2+' - ''+RTRIM(@TypeName2)+''(''+LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen2/2 else @CLen2 end))+'')''if @Type1 = @Type2set @DiffStructure=@DiffStructure -- Do nothing. Cannot invert predicateelseset @DiffStructure = 1fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,@CLen1, @CLen2, @Type1, @Type2enddeallocate Diffif @DiffStructure = 1insert into #DiffStructure values (@PrevTName)'exec (@sqlStr)if (select count(*) from #DiffStructure) > 0beginprint CHAR(10)+'The table(s) have the same name and different structure in the databases:'select distinct * from #DiffStructure delete from #TabToCheck where name in (select * from #DiffStructure)endelseprint CHAR(10)+'There are no tables with the same name and structural differences in the databases'+CHAR(10)+CHAR(10)if @OnlyStructure = 1beginprint 'The option ''Only compare structures'' was specified. End of work.'returnendexec ('declare @Name sysname select @Name=d1o.namefrom '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''and d1o.name not in (''dtproperties'') and d1o.name in (select * from #TabToCheck)')if @@rowcount = 0beginprint 'There are no tables with the same name and structure in the databases to compare. End of work.'returnend------------------------------------------------------------------------------------------- Comparing data ------------------------------------------------------------------------------------------- ##CompareStr - will be used to pass comparing strings into dynamic script-- to execute the stringif exists (select * from tempdb.dbo.sysobjects where name like '##CompareStr%')drop table ##CompareStrcreate table ##CompareStr (Ind int, CompareStr varchar(8000))if exists (select * from tempdb.dbo.sysobjects where na
me like '#DiffTables%')drop table #DiffTablescreate table #DiffTables (Name sysname)if exists (select * from tempdb.dbo.sysobjects where name like '#IdenticalTables%')drop table #IdenticalTablescreate table #IdenticalTables (Name sysname)if exists (select * from tempdb.dbo.sysobjects where name like '#EmptyTables%')drop table #EmptyTablescreate table #EmptyTables (Name sysname)if exists (select * from tempdb.dbo.sysobjects where name like '#NoPKTables%')drop table #NoPKTablescreate table #NoPKTables (Name sysname)if exists (select * from tempdb.dbo.sysobjects where name like '#IndList1%')truncate table #IndList1else create table #IndList1 (IndId int, IndStatus int,KeyAndStr varchar(7000), KeyCommaStr varchar(1000))if exists (select * from tempdb.dbo.sysobjects where name like '#IndList2%')truncate table #IndList2elsecreate table #IndList2 (IndId smallint, IndStatus int,KeyAndStr varchar(7000), KeyCommaStr varchar(1000))print Replicate('-',51)print 'Comparing data in tables with indentical structure:'print Replicate('-',51)---------------------------------------------------------------------------------------------- Cursor for all tables in dbs (or for all specified tables if parameter @TabList is passed)--------------------------------------------------------------------------------------------declare @SqlStrGetListOfKeys1 varchar(8000)declare @SqlStrGetListOfKeys2 varchar(8000)declare @SqlStrGetListOfColumns varchar(8000)declare @SqlStrCompareUKeyTables varchar(8000)declare @SqlStrCompareNonUKeyTables varchar(8000)set @SqlStrGetListOfKeys1 = 'declare @sqlStr varchar(8000)declare @ExecSqlStr varchar(8000)declare @PrintSqlStr varchar(8000)declare @Tab varchar(12declare @d1User varchar(12declare @d2User varchar(12declare @KeyAndStr varchar(8000) declare @KeyCommaStr varchar(8000) declare @AndStr varchar(8000) declare @Eq varchar(8000) declare @IndId intdeclare @IndStatus intdeclare @CurrIndId smallintdeclare @CurrStatus intdeclare @UKey sysname declare @Col varchar(12declare @LastUsedCol varchar(12declare @xType intdeclare @Len intdeclare @SelectStr varchar(8000) declare @ExecSql nvarchar(1000) declare @NotInDB1 bit declare @NotInDB2 bit declare @NotEq bit declare @Numb intdeclare @Cnt1 intdeclare @Cnt2 intset @Numb = 0declare @StrInd intdeclare @i intdeclare @PrintStr varchar(8000)declare @ExecStr varchar(8000)declare TabCur cursor for select d1o.name, d1u.name, d2u.name from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o,'+@db1+'.dbo.sysusers d1u, '+@db2+'.dbo.sysusers d2u where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''and d1o.uid = d1u.uid and d2o.uid = d2u.uid and d1o.name not in (''dtproperties'') and d1o.name in (select * from #TabToCheck)order by 1open TabCur fetch next from TabCur into @Tab, @d1User, @d2User while @@fetch_status = 0 begin set @Numb = @Numb + 1print Char(13)+Char(10)+LTRIM(STR(@Numb))+''. TABLE: [''+@Tab+''] ''set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db1+'.[''+@d1User+''].[''+@Tab+'']''exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt1 outputprint CHAR(10)+STR(@Cnt1)+'' rows in '+@db1+'''set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db2+'.[''+@d2User+''].[''+@Tab+'']''exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt2 outputprint STR(@Cnt2)+'' rows in '+@db2+'''if @Cnt1 = 0 and @Cnt2 = 0beginexec ('' insert into #EmptyTables values(''''[''+@Tab+'']'''')'') goto NextTabendset @KeyAndStr = '''' set @KeyCommaStr = '''' set @NotInDB1 = 0set @NotInDB2 = 0 set @NotEq = 0set @KeyAndStr = '''' set @KeyCommaStr = '''' truncate table #IndList1declare UKeys cursor fast_forward for 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 where i.id = o.id and o.name = @Taband (i.status & 2)<>0 and k.id = o.id and k.indid = i.indid and c.id = o.id and c.colid = k.colid order by i.indid, c.nameopen UKeys fetch next from UKeys into @IndId, @IndStatus, @UKey, @xTypeset @CurrIndId = @IndIdset @CurrStatus = @IndStatuswhile @@fetch_status = 0 begin if @KeyAndStr <> ''''begin set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10) set @KeyCommaStr = @KeyCommaStr + '', '' end if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarcharbeginset @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''endif @xType = 173 or @xType = 165 -- binary, varbinarybeginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoneybeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 106 or @xType = 108 -- int, decimal, numericbeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 62 or @xType = 59 -- 62 - float, 59 - realbegin set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bitbeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 36 -- 36 - id beginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d2.[''+@UKey+''] END''endelse if @xType = 61 or @xType = 58 -- datetime, smalldatetimebeginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''endelse if @xType = 189 -- timestamp (189) beginset @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''endelse if @xType = 98 -- SQL_variantbeginset @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''endset @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey fetch next from UKeys into @IndId, @IndStatus, @UKey, @xTypeif @IndId <> @CurrIndIdbegininsert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)set @CurrIndId = @IndIdset @CurrStatus = @IndStatusset @KeyAndStr = ''''set @KeyCommaStr = '''' endend deallocate UKeys insert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)'set @SqlStrGetListOfKeys2 = 'set @KeyAndStr = '''' set @KeyCommaStr = '''' truncate table #IndList2declare UKeys cursor fast_forward for 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 where i.id = o.id and o.name = @Taband (i.status & 2)<>0 and k.id = o.id and k.indid = i.indid and c.id = o.id and c.colid = k.colid order by i.indid, c.nameopen UKeys fetch next from UKeys into @IndId, @IndStatus, @UKey, @xTypeset @CurrIndId = @IndIdset @CurrStatus = @IndStatuswhile @@fetch_status = 0 begin if @KeyAndStr <> ''''begin set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10) set @KeyCommaStr = @KeyCommaStr + '', '' end if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarcharbeginset @KeyAndStr
= @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''endif @xType = 173 or @xType = 165 -- binary, varbinarybeginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoneybeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 106 or @xType = 108 -- int, decimal, numericbeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 62 or @xType = 59 -- 62 - float, 59 - realbegin set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bitbeginset @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''endelse if @xType = 36 -- 36 - id beginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d1.[''+@UKey+''] END=''+''CASE WHEN d2.[''+@UKey+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d2.[''+@UKey+''] END''endelse if @xType = 61 or @xType = 58 -- datetime, smalldatetimebeginset @KeyAndStr = @KeyAndStr +'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''endelse if @xType = 189 -- timestamp (189) beginset @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''endelse if @xType = 98 -- SQL_variantbeginset @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''endset @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey fetch next from UKeys into @IndId, @IndStatus, @UKey, @xTypeif @IndId <> @CurrIndIdbegininsert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)set @CurrIndId = @IndIdset @CurrStatus = @IndStatusset @KeyAndStr = ''''set @KeyCommaStr = '''' endend deallocate UKeys insert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)set @KeyCommaStr = nullselect @KeyCommaStr=i1.KeyCommaStr from #IndList1 i1join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStrwhere (i1.IndStatus & 204<> 0 and (i2.IndStatus & 204<>0if @KeyCommaStr is null set @KeyCommaStr = (select top 1 i1.KeyCommaStr from #IndList1 i1join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr)set @KeyAndStr = (select TOP 1 KeyAndStr from #IndList1 where KeyCommaStr = @KeyCommaStr)if @KeyCommaStr is nullset @KeyCommaStr = ''''if @KeyAndStr is nullset @KeyAndStr = '''''set @SqlStrGetListOfColumns = 'set @AndStr = ''''set @StrInd = 1declare Cols cursor local fast_forward for select c.name, c.xtype, c.length from '+@db1+'.dbo.sysobjects o, '+@db1+'.dbo.syscolumns cwhere o.id = c.id and o.name = @Tab and CHARINDEX(c.name, @KeyCommaStr) = 0open Cols fetch next from Cols into @Col, @xType, @lenwhile @@fetch_status = 0 begin if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarcharbeginset @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''endif @xType = 173 or @xType = 165 -- binary, varbinarybeginset @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@Col+''] END ''endelse if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoneybeginset @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 971428763405345098745 ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null THEN 971428763405345098745 ELSE d2.[''+@Col+''] END ''endelse if @xType = 106 or @xType = 108 -- int, decimal, numericbeginset @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@Col+''] END ''endelse if @xType = 62 or @xType = 59 -- 62 - float, 59 - realbegin set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@Col+''] END ''endelse if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bitbeginset @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 99999 ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null THEN 99999 ELSE d2.[''+@Col+''] END ''endelse if @xType = 36 -- 36 - id beginset @Eq = ''CASE WHEN d1.[''+@Col+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d1.[''+@Col+''] END=''+''CASE WHEN d2.[''+@Col+''] is null''+'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+'' ELSE d2.[''+@Col+''] END''endelse if @xType = 61 or @xType = 58 -- datetime, smalldatetimebeginset @Eq =''CASE WHEN d1.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@Col+''],109) END=''+''CASE WHEN d2.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@Col+''],109) END ''endelse if @xType = 34beginset @Eq = ''ISNULL(DATALENGTH(d1.[''+@Col+'']),0)=ISNULL(DATALENGTH(d2.[''+@Col+'']),0) '' endelse if @xType = 35 or @xType = 99 -- text (35),ntext (99) beginset @Eq = ''ISNULL(SUBSTRING(d1.[''+@Col+''],1,DATALENGTH(d1.[''+@Col+''])),''''!#null$'''')=ISNULL(SUBSTRING(d2.[''+@Col+''],1,DATALENGTH(d2.[''+@Col+''])),''''!#null$'''') ''endelse if @xType = 189 beginif '+STR(@NoTimestamp)+' = 0 set @Eq = ''d1.[''+@Col+'']=d2.[''+@Col+''] ''elseset @Eq = ''1=1''endelse if @xType = 98 -- SQL_variantbeginset @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''endif @AndStr = ''''set @AndStr = @AndStr + CHAR(10) + '' '' + @Eq elseif len(@AndStr) + len('' and '' + @Eq)<8000set @AndStr = @AndStr + '' and '' + CHAR(10) + '' '' + @Eq elsebeginset @StrInd = @StrInd + 1Insert into ##CompareStr values(@StrInd,@AndStr)set @AndStr = '' and '' + @Eq endfetch next from Cols into @Col, @xType, @len end deallocate Cols 'set @SqlStrCompareUKeyTables = 'if @KeyAndStr <> ''''beginset @SelectStr = ''SELECT ''+ @KeyCommaStr+'' INTO ##NotInDb2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 ''+ '' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 ''+ '' WHERE ''+CHAR(10)+@KeyAndStr+'')''if '+STR(@VerboseLevel)+' = 1print CHAR(10)+''To find rows that are in '+@db1+', but are not in db2:''+CHAR(10)+REPLACE (@SelectStr, ''into ##NotInDB2'','''')exec (@SelectStr) if @@rowcount > 0 set @NotInDB2 = 1 set @SelectStr = ''SELECT ''+@KeyCommaStr+'' INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+ '' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ''+ '' WHERE ''+CHAR(10)+@KeyAndStr+'')'' if '+STR(@VerboseLevel)+' = 1print CHAR(10)+''To find rows that are in '+@db2+', but are not in '+@db1+':''+CHAR(10)+REPLACE (@SelectStr, ''into ##NotInDB1'','''')exec (@SelectStr) if @@rowcount > 0 set @NotInDB1 = 1 -- if there are non-key columnsif @AndStr <> '''' beginset @PrintStr = '' Print ''set @ExecStr = '' exec (''set @SqlStr = ''''Insert into ##CompareS
tr values(1,''SELECT ''+ @KeyCommaStr+'' INTO ##NotEq FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+ '' INNER JOIN '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ON ''+CHAR(10)+@KeyAndStr+CHAR(10)+''WHERE not('') -- Adding last string in temp table containing a comparing string to executeset @StrInd = @StrInd + 1Insert into ##CompareStr values(@StrInd,@AndStr+'')'')set @i = 1while @i <= @StrIndbeginset @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)if @ExecStr <> '' exec (''set @ExecStr = @ExecStr + ''+''if @PrintStr <> '' Print ''set @PrintStr = @PrintStr + ''+''set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotEq'''','''''''') ''set @i = @i + 1endset @ExecStr = @ExecStr + '') ''set @ExecSqlStr = @SqlStr + @ExecStr set @PrintSqlStr = @SqlStr + '' Print CHAR(10)+''''To find rows that are different in non-key columns:'''' ''+@PrintStr if '+STR(@VerboseLevel)+' = 1exec (@PrintSqlStr)exec (@ExecSqlStr)if @@rowcount > 0 set @NotEq = 1 endelseif '+STR(@VerboseLevel)+' = 1print CHAR(10)+''There are no non-key columns in the table''truncate table ##CompareStrif @NotInDB1 = 1 or @NotInDB2 = 1 or @NotEq = 1begin print CHAR(10)+''Data are different''if @NotInDB2 = 1 and '+STR(@NumbToShow)+' > 0beginprint ''These key values exist in '+@db1+', but do not exist in '+@db2+': ''set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''exec (@SelectStr)endif @NotInDB1 = 1 and '+STR(@NumbToShow)+' > 0beginprint ''These key values exist in '+@db2+', but do not exist in '+@db1+': ''set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''exec (@SelectStr)endif @NotEq = 1 and '+STR(@NumbToShow)+' > 0beginprint ''Row(s) with these key values contain differences in non-key columns: ''set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotEq''exec (@SelectStr) endexec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'') end elsebeginprint CHAR(10)+''Data are identical''exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'') endif exists (select * from tempdb.dbo.sysobjects where name like ''##NotEq%'')drop table ##NotEqend else 'set @SqlStrCompareNonUKeyTables = 'beginexec (''insert into #NoPKTables values(''''[''+@Tab+'']'''')'')set @PrintStr = '' Print ''set @ExecStr = '' exec (''set @SqlStr = ''''Insert into ##CompareStr values(1,''SELECT ''+'' * INTO ##NotInDB2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 WHERE '')set @StrInd = @StrInd + 1Insert into ##CompareStr values(@StrInd,@AndStr+'')'')set @i = 1while @i <= @StrIndbeginset @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)if @ExecStr <> '' exec (''set @ExecStr = @ExecStr + ''+''if @PrintStr <> '' Print ''set @PrintStr = @PrintStr + ''+''set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB2'''','''''''') ''set @i = @i + 1endset @ExecStr = @ExecStr + '') ''set @ExecSqlStr = @SqlStr + @ExecStr set @PrintSqlStr = @SqlStr +'' Print CHAR(10)+''''To find rows that are in '+@db1+', but are not in '+@db2+':'''' ''+@PrintStr if '+STR(@VerboseLevel)+' = 1exec (@PrintSqlStr)exec (@ExecSqlStr)if @@rowcount > 0 set @NotInDB2 = 1 delete from ##CompareStr where ind = 1set @PrintStr = '' Print ''set @ExecStr = '' exec (''set @SqlStr = ''''Insert into ##CompareStr values(1,''SELECT ''+'' * INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 WHERE '')set @i = 1while @i <= @StrIndbeginset @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)if @ExecStr <> '' exec (''set @ExecStr = @ExecStr + ''+''if @PrintStr <> '' Print ''set @PrintStr = @PrintStr + ''+''set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB1'''','''''''') ''set @i = @i + 1endset @ExecStr = @ExecStr + '') ''set @ExecSqlStr = @SqlStr + @ExecStr set @PrintSqlStr = @SqlStr +'' Print CHAR(10)+''''To find rows that are in '+@db2+', but are not in '+@db1+':'''' ''+@PrintStr if '+STR(@VerboseLevel)+' = 1exec (@PrintSqlStr)exec (@ExecSqlStr)if @@rowcount > 0 set @NotInDB1 = 1 truncate table ##CompareStrif @NotInDB1 = 1 or @NotInDB2 = 1begin print CHAR(10)+''Data are different''if @NotInDB2 = 1 and '+STR(@NumbToShow)+' > 0beginprint ''The row(s) exist in '+@db1+', but do not exist in '+@db2+': ''set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''exec (@SelectStr)endif @NotInDB1 = 1 and '+STR(@NumbToShow)+' > 0beginprint ''The row(s) exist in '+@db2+', but do not exist in '+@db1+': ''set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''exec (@SelectStr)endexec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'') end elsebeginprint CHAR(10)+''Data are identical''exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'') endendif exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB1%'')drop table ##NotInDB1if exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB2%'')drop table ##NotInDB2NextTab:fetch next from TabCur into @Tab, @d1User, @d2User end deallocate TabCur 'exec (@SqlStrGetListOfKeys1+@SqlStrGetListOfKeys2+@SqlStrGetListOfColumns+@SqlStrCompareUKeyTables+@SqlStrCompareNonUKeyTables)print ' 'SET NOCOUNT OFFif (select count(*) from #NoPKTables) > 0beginselect name as 'Table(s) without Unique key:' from #NoPKTables endif (select count(*) from #DiffTables) > 0beginselect name as 'Table(s) with the same name & structure, but different data:' from #DiffTables endelseprint CHAR(10)+'No tables with the same name & structure, but different data'+CHAR(10)if (select count(*) from #IdenticalTables) > 0beginselect name as 'Table(s) with the same name & structure and identical data:' from #IdenticalTables endif (select count(*) from #EmptyTables) > 0beginselect name as 'Table(s) with the same name & structure and empty in the both databases:' from #EmptyTables enddrop table #TabToCheckdrop table ##CompareStrdrop table #DiffTablesdrop table #IdenticalTablesdrop table #EmptyTablesdrop table #NoPKTablesdrop table #IndList1drop table #IndList2return

Really, we’re talking about her hair? Okay then. I like it. Those two gold medals around her neck really bring out the highlights. #gabbydouglas

Posted on by

Good for you, Gabby!

http://msn.foxsports.com/olympics/gymnastics/story/gabby-douglas-gold-medalis…

Women’s gymnastics all-around champion Gabby Douglas likes her hairstyle just fine, thanks.

The 16-year-old said Sunday she was a little confused when she logged onto her computer after winning her second gold medal in three days and discovered people were debating her pulled-back look.

”I don’t know where this is coming from. What’s wrong with my hair?” said Douglas, the first U.S. gymnast to win gold in team and all-around competition. ”I’m like, `I just made history and people are focused on my hair?’ It can be bald or short, it doesn’t matter about (my) hair.”

Douglas uses gel, clips and a ponytail holder to keep things in place while she competes, a style she’s worn for years.

”Nothing is going to change,” she said. ”I’m going to wear my hair like this during beam and bar finals. You might as well just stop talking about it.”

The bubbly teenager is the first African-American gymnast to win her sport’s biggest prize. She had no idea she was lighting up social media until she Googled herself hours after winning her gold medal.

”I don’t think people should be worried about that,” she said. ”We’re all champions and we’re all winners. I just say that it’s kind of, a stupid and crazy thought to think about my hair.”