I'm probably stupid, but can I play a tab in that is in standard tuning, the same way with a guitar tuned down to C#? Will everything just sound lower? Or do I need to get a tab in that tuning?
If all the strings are tuned down three half-steps then yes, you can play the tab exactly the same and it will just sound lower — specifically, the song will be transposed 3 half-steps lower. Naturally, any other instruments would need to play lower to accompany you as well.
If you're playing Drop C# (everything down 1 half-step except the low string which is down 3) then you would need to adjust the tab, most likely by playing everything on the lowest string 2 frets higher than the tab indicates. If that doesn't work you would probably need to rewrite the tab to fit some other possible way to play the song.
Capo works magic on the stage. Let's say you want to do some Rush and some Sabbath in the same show. Capo gives you the flexibility to quickly switch tunings without the hassle of having the whole band tune up and down.
This can be done on bass, and if set up well with a medium to high action and thicker gauge strings gives a nice thick mellow as molasses guitar tone, heavy as hell and downright mean with distortion.
You can also experiment and play weirder tunings - Soundgarden's Rusty Cage (Drop B), Melvins Boris (Drop A). The answer is to get a capo, they're cheap, and experiment and see if it works for you.
Tab is going to be a lot harder because you have to transpose all notes 3 frets up. It helps to consider the capo on 3 as the open EADGBE and play your F on the 4th fret and think of it as a 1, until you get good at switching beck and forth, a skill that comes with practice and time.
C# is a good tuning because you and the capo get access to some of the most used drop tunings for songs: Nirvana - D standard, Slayer -Eb standard. C#,D,Eb, and E. An increased range on the low end but you will have to use some trickery to fake solos on 21-24 frets in standard tuning.
I've personally always liked the Soundgarden approach of 2 different guitars in 2 different tunings.