You can change the 'notifications' sound by going to:
Settings>Sound & Display>Notification Settings
It won't be just
for texts, it'll be for whatever you have your phone set to notify you on. I have mine set for texts, missed calls, voice-mails and reminders. I disabled it for e-mails, I prefer to check my e-mail a few times a day, I don't need to be notified every time.