compare8

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

UNION ALL

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

UNION ALL

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

ORDER BY location, table_name;