db tables

SELECT
table_name,
MAX(CASE WHEN db = 'COMET_US_REF' THEN 'YES' ELSE 'NO' END) AS IN_COMET_US_REF,
MAX(CASE WHEN db = 'GLOBAL_COMET_US_1' THEN 'YES' ELSE 'NO' END) AS IN_GLOBAL_COMET_US_1,
MAX(CASE WHEN db = 'GLOBAL_COMET_US_2' THEN 'YES' ELSE 'NO' END) AS IN_GLOBAL_COMET_US_2,
MAX(CASE WHEN db = 'GLOBAL_COMET_US_3' THEN 'YES' ELSE 'NO' END) AS IN_GLOBAL_COMET_US_3,
COUNT(DISTINCT db) AS total_dbs_present
FROM (
SELECT 'COMET_US_REF' AS db, name AS table_name FROM COMET_US_REF.dbo.sysobjects WHERE type = 'U'
UNION ALL
SELECT 'GLOBAL_COMET_US_1' AS db, name AS table_name FROM GLOBAL_COMET_US_1.dbo.sysobjects WHERE type = 'U'
UNION ALL
SELECT 'GLOBAL_COMET_US_2' AS db, name AS table_name FROM GLOBAL_COMET_US_2.dbo.sysobjects WHERE type = 'U'
UNION ALL
SELECT 'GLOBAL_COMET_US_3' AS db, name AS table_name FROM GLOBAL_COMET_US_3.dbo.sysobjects WHERE type = 'U'
) all_tables
GROUP BY table_name
HAVING COUNT(DISTINCT db) > 1 AND COUNT(DISTINCT db) < 4
ORDER BY total_dbs_present DESC, table_name;