On Jul 17, 12:33 pm, Omid Golban
<OmidGol...@discussions.microsoft.com> wrote:

Management Studio doesn't have that capability built in; there are
several third party tools available. I use RedGate's SQL Compare, but
I believe Apex makes one as well.

You can script the databases out to seperate folders (one script per object),
and then use WinDiff (free) or Beyond Compare (~$30) to highlight
differences. If you want to pay for it, Red Gate SQL Compare is better,
doesn't require manual scripting, and can also create scripts to deploy
differences.

It is possible to do comparisons as queries.  The only limit is your
imagination!  And it's free.

Some examples:

-- Objects in db1 not in db2
SELECT name
FROM db1.sys.objects
WHERE is_ms_shipped = 0
EXCEPT
SELECT name
FROM db2.sys.objects
WHERE is_ms_shipped = 0

-- Objects in db2 not in db1
SELECT name
FROM db2.sys.objects
WHERE is_ms_shipped = 0
EXCEPT
SELECT name
FROM db1.sys.objects
WHERE is_ms_shipped = 0

-- Tables where columns or data-types are different
SELECT OBJECT_NAME( object_id, DB_ID( 'db1' ) ), name, TYPE_NAME(
user_type_id ), max_length, precision, scale, is_nullable
FROM db1.sys.columns
EXCEPT
SELECT OBJECT_NAME( object_id, DB_ID( 'db1' ) ), name, TYPE_NAME(
user_type_id ), max_length, precision, scale, is_nullable
FROM db2.sys.columns

-- Vice versa
SELECT OBJECT_NAME( object_id, DB_ID( 'db1' ) ), name, TYPE_NAME(
user_type_id ), max_length, precision, scale, is_nullable
FROM db2.sys.columns
EXCEPT
SELECT OBJECT_NAME( object_id, DB_ID( 'db1' ) ), name, TYPE_NAME(
user_type_id ), max_length, precision, scale, is_nullable
FROM db1.sys.columns

USE db1
-- Be careful using OBJECT_SCHEMA_NAME and OBJECT_NAME, which should be
called in relevant database, or pass in DB_ID argument,
-- eg SELECT OBJECT_NAME( object_id, DB_ID( 'db1' ) )
-- Objects with text ( ie stored procs, views, triggers etc ) exist in both
databases or text is different
SELECT DB_NAME() AS database_name, OBJECT_SCHEMA_NAME( object_id ) AS
schema_name, OBJECT_NAME( object_id ) AS object_name, CHECKSUM( definition )
AS object_checksum
INTO #sql_modules
FROM sys.sql_modules
GO

USE db2

INSERT INTO #sql_modules
SELECT DB_NAME() AS database_name, OBJECT_SCHEMA_NAME( object_id ) AS
schema_name, OBJECT_NAME( object_id ) AS object_name, CHECKSUM( definition )
AS object_checksum
FROM sys.sql_modules
GO

-- Query the differences

SELECT schema_name, object_name, object_checksum
FROM #sql_modules
WHERE database_name = 'db1'
EXCEPT
SELECT schema_name, object_name, object_checksum
FROM #sql_modules
WHERE database_name = 'db2'

SELECT schema_name, object_name, object_checksum
FROM #sql_modules
WHERE database_name = 'db2'
EXCEPT
SELECT schema_name, object_name, object_checksum
FROM #sql_modules
WHERE database_name = 'db1'

HTH
wBob

On Jul 17, 11:33 am, Omid Golban
<OmidGol...@discussions.microsoft.com> wrote:

Using a tool is typically much cheaper.