Identify faulty sub-query (subquery returned more than 1 value)
I think the best way would be to "prove" that your subqueries - that are compared with a value - do always return one or no value.

By making sure they all have either:


TOP (1) ... ORDER BY ... (or the equivalent modern ORDER BY ... FETCH NEXT 1 ROWS ONLY)
WHERE clause that uses the primary or a unique key, e.g.: WHERE table_id = 496
HAVING clause that uses the primary or a unique key
GROUP BY () or an aggregate function in the HAVING or SELECT clause (this ensures that the subquery will return 1 or 0 rows) 


This of course is not at all trivial with complex queries (example: nested derived tables or ctes and/or nested correlated subqueries) and even harder when the unique keys are not documented.

It's not trivial because for the most complex queries, you may have to figure out the unique keys of all the derived tables/ctes/subqueries which may involve joins and more nested subqueries (and for the queries that involve joins, the foreign key constraints may be needed as well to find the unique keys.)