compare9

-- Tables ONLY in GLOBAL_COMET_US_1 (not present in GLOBAL_COMET_US_2)
SELECT 'ONLY_IN_US_1' AS location, name AS table_name
FROM   GLOBAL_COMET_US_1.dbo.sysobjects
WHERE  type = 'U'
  AND  name NOT IN (SELECT name FROM GLOBAL_COMET_US_2.dbo.sysobjects WHERE type = 'U')

UNION ALL

-- Tables ONLY in GLOBAL_COMET_US_2 (not present in GLOBAL_COMET_US_1)
SELECT 'ONLY_IN_US_2' AS location, name
FROM   GLOBAL_COMET_US_2.dbo.sysobjects
WHERE  type = 'U'
  AND  name NOT IN (SELECT name FROM GLOBAL_COMET_US_1.dbo.sysobjects WHERE type = 'U')

UNION ALL

-- Tables that exist IN BOTH databases
SELECT 'IN_BOTH' AS location, name
FROM   GLOBAL_COMET_US_1.dbo.sysobjects o1
WHERE  type = 'U'
  AND  EXISTS (SELECT 1 
               FROM   GLOBAL_COMET_US_2.dbo.sysobjects o2 
               WHERE  o2.name = o1.name 
                 AND  o2.type = 'U')

ORDER BY location, table_name;