A bad second factor is better than no second factor.
I enabled TOTP on every account I have that supports it, which comes to about 2 out of every 5 services. I'm not going to leave the other 60% with only one factor just because SMS can be exploited, which the consensus in this thread seems to be advising everyone to do.
If someone can exploit your SMS, it's possible they can use that to social engineer their way into a password resets with services. (I forgot may password but I still have my phone.) So I would say a bad second factor can be strictly worse than no second factor.
You're describing single factor, not two factor. If you can change the password with SMS alone, it's not multi-factor. I plainly stated that exception two comments ago.
Except you have no way of knowing if that will be the case ahead of time. Unless the first thing you do after enabling 2FA is to social engineer a password reset for your account? Even then that doesn't guarantee that there isn't a more clueless service rep that will make a mistake.
Asking before you sign up, "will you allow my account to be hacked through social engineering?" isn't going to an answer other than no. Even if the answer is possibly yes.
But then let's please move the discussion from "Is SMS a good or bad second factor?" to "SMS is a mediocre second factor, and a terrible single factor. For this service, is it a second or single factor?"
You're incorrectly assuming that you can predict a site will never allow password reset via SMS only.
You can check if they appear to allow it today. Not perfectly, as they may have multiple variants and depending on other factors you might get presented with one or the other.
But you have no way to predict if next month a PM there decides their current password reset was too cumbersome and they change it to SMS-only. If you had a phone# on file, you're now suddenly vulnerable.
SMS 2FA was vaguely reasonable before TOTP applications and smartphones capable of running them were widely available. That's no longer the case.