By RAW, as far as I can tell, it does detect most of these things, automatically; doesn't detect Nystul's because it specifically obscures the magical aura, but otherwise yes. I find that far too powerful for a 0-level spell myself, and have a houserule primarily dealing with illusion spells in regards to it:
Detect magic does not automatically reveal illusion auras of spells higher level than it; instead, it grants a saving throw with a +4 bonus to disbelieve the illusion. Under no circumstances does detect magic reveal a magical aura on an object that the caster cannot see due to invisibility or similar spells, or the aura of the invisibility spell itself. In order to determine the location of a magical aura, it must remain still for three full rounds of concentration; any movement of 5 feet or more spoils the attempt and requires the caster to begin concentrating once again.
This rule allows it to function to detect illusion auras if it's heightened to the same level as those illusions, but not otherwise. Because otherwise, most illusions would be relatively useless against anything with detect magic up, to say nothing of arcane sight. Sure, it doesn't auto-reveal that they're illusions, but if an entire wall is glowing magical with an illusion aura, that's suspicious and needs investigation, which will immediately reveal the truth.
