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;