Large Number of Tables - Find Values that Exist in More than One Table

Question

I am trying to categorize one element of a very large dataset by using nuanced, and at times potentially overlapping logic. Currently, I have roughly 18 temp tables that I've populated based on each respective categorization logic.
What I need to do is find all values that occur in more than one table and are thus need their categorization logic tweaked so as to not overlap categories.
Would I be able to run some sort of massive join between all of these tables to try to find overlapping categorizations? How would I go about structuring that? Are there any other alternative methods I should consider?

Answer 1

WITH
  cte1([table_name],[value]) AS (
    SELECT 'table1',[value] FROM table1 UNION ALL
    SELECT 'table2',[value] FROM table2 UNION ALL
    SELECT 'table3',[value] FROM table3 UNION ALL
    SELECT 'table4',[value] FROM table4
  )
 ,cte2 AS (
    SELECT
      [table_name]
     ,[value]
     ,[count_by_value] = COUNT(*) OVER(PARTITION BY [value])
    FROM cte1
 )
SELECT *
FROM cte2
WHERE [count_by_value] > 1