By looping through the INFORMATION_SCHEMA.TABLES collection and construct the T-SQL
along the way, you have a simple and easy method for comparing databases.
USE = your_source_db-for_comparison_here --(to select the right tables from sys.objects)
GO
DECLARE @compareschema NVARCHAR(100)
DECLARE @comparetable1 NVARCHAR(400), @comparetable2 NVARCHAR(400)
DECLARE @dbtocompare NVARCHAR(200), @command NVARCHAR(4000)
SET @dbtocompare ='dbtocompare' -- db to compare
-- or set @dbtocompare = 'LINKEDSQLSERVER.dbtocompare' -- or db plus linked server if required
DECLARE tablenames_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT TABLE_SCHEMA,TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN tablenames_cursor
FETCH NEXT FROM tablenames_cursor INTO @compareschema, @comparetable1
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @comparetable1= @compareschema + '.' + @comparetable1
SET @comparetable2 = @dbtocompare + '.' + @comparetable1
PRINT @comparetable1 -- print tables in the messages window/tab
PRINT @comparetable2
SET @command = 'select ''' + @comparetable1 + ''' CompTable, * from
(select * from ' + @comparetable1 + '
except
select * from ' + @comparetable2 + ') as CompTable
union all
select ''' + @comparetable2 + ''' CompTable, * from
(select * from ' + @comparetable2 + '
except
select * from ' + @comparetable1 +') as CompTable '
EXEC SP_EXECUTESQL @command
FETCH NEXT FROM tablenames_cursor INTO @compareschema, @comparetable1
END
CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor
GO
DECLARE @compareschema NVARCHAR(100)
DECLARE @comparetable1 NVARCHAR(400), @comparetable2 NVARCHAR(400)
DECLARE @dbtocompare NVARCHAR(200), @command NVARCHAR(4000)
SET @dbtocompare ='dbtocompare' -- db to compare
-- or set @dbtocompare = 'LINKEDSQLSERVER.dbtocompare' -- or db plus linked server if required
DECLARE tablenames_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT TABLE_SCHEMA,TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN tablenames_cursor
FETCH NEXT FROM tablenames_cursor INTO @compareschema, @comparetable1
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @comparetable1= @compareschema + '.' + @comparetable1
SET @comparetable2 = @dbtocompare + '.' + @comparetable1
PRINT @comparetable1 -- print tables in the messages window/tab
PRINT @comparetable2
SET @command = 'select ''' + @comparetable1 + ''' CompTable, * from
(select * from ' + @comparetable1 + '
except
select * from ' + @comparetable2 + ') as CompTable
union all
select ''' + @comparetable2 + ''' CompTable, * from
(select * from ' + @comparetable2 + '
except
select * from ' + @comparetable1 +') as CompTable '
EXEC SP_EXECUTESQL @command
FETCH NEXT FROM tablenames_cursor INTO @compareschema, @comparetable1
END
CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor
Because of the 'except' this construction works for SQL Server 2005 and higher only, and you can't use the the -older- text, ntext and image datatypes in comparisons.
You have to convert them to the newer varchar(max), nvarchar(max), and varbinary(max) data types first. (see my post on SQL Server 2000 post upgrade steps)
This is the error you get when you do use older data types
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 402, Level 16, State 1, Line 1
The data types text and text are incompatible in the is operator.
