Extension on post from Jeff on comparing tables.
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.
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.