Compare data in all user tables between two databases
/*
This script-generating script will output t-sql script for comparing data in tables for two databases. Some columns with special data types are excluded. Maybe they can be casted to varchar, but its not done here. Tip: Execute this script in the database with least tables.
Remember to run output script in both directions since it uses "except" (switch db1<->db2).
Will report diff row count and rows themselves. Can be heavy if big db.
Use "Copy with headers" in result below for correct script
Written on MsSql 2008r2
*/declare
@db1 varchar(100) = 'Db1',
@db2 varchar(100) = 'Db2'
declare @excludedTypes as table (TypeName varchar(256))declare @excludedColumns as table (ColumnName varchar(256))
insert @excludedTypes values ('image'),('text'),('ntext'),('xml'),('timestamp') insert @excludedColumns values ('ColumnToIgnore1'),('ColumnToIgnore2')
select distinct
'select '+
left(dt,len(dt)-1)+ --as ColumnNames
' into #tmp'+cast(t.object_id as varchar(100))+' from '+@db1+'.['+s.name+'].['+t.name +']'as [set nocount on ; /*FirstTable],
' except ' as [Except],
'select '+
left(dt,len(dt)-1)+ --as ColumnNames
' from '+@db2+'.['+s.name+'].['+t.name+']' as [SecondTable],
'; declare @count'+cast(t.object_id as varchar(100))+' int=(select count(*) from #tmp'+cast(t.object_id as varchar(100))+'); if @count'+cast(t.object_id as varchar(100))+'>0 select * from #tmp'+cast(t.object_id as varchar(100))+';' as [Statement],
' drop table #tmp'+cast(t.object_id as varchar(100))+'; print cast(@count'+cast(t.object_id as varchar(100))+' as varchar(100))+'' diffs in '+s.name+'.'+t.name +''';' as [Statement */]
from sys.tables t
join sys.schemas s
on s.schema_id=t.schema_id
cross apply
(
select '['+col.name +'],'
from sys.columns as col
join sys.types ty
on ty.user_type_id=col.user_type_id
where t.object_id=col.object_id
and ty.name not in (select TypeName from @excludedTypes)
and col.name not in (select columnname from @excludedColumns)
for xml path('')
) as c(dt)
Comments