I see you are a newer user here - welcome to Android Central! A great way to download different notification tones - or ringtones, wallpapers, and icon packs- is to download the Zedge app. It's free and loads of fun to use!
If you are talking about not all of your apps to have the same tone, go into your SMS settings to see if you can change the notification tone.