That would only be practically useful in an environment devoid of all other electronics. How would one tell between the non-transmitting spy cam and normal household electronics
By having some spatial/angular resolution. You'd need a large directional antenna or multiple antennas to have something similar to a phased array radar, but passively (i.e. listening only).
What you really need is a Non-Linear Junction Detector. These can detect an unshielded device with transistors, even if it's off.