This is one of those things that I always mark up to studio fantasy lore that doesn't really happen that much in real life. Here are my thoughts on the matter.
First, there are obvious tuning differences that you'll find in, say, a heavy rock vs a fusion jazz record. The heavy rock record will most likely have the drums tuned to their lowest tuning ranges while the fusion record will most likely call for a wider range of voicings, particularly for the toms. You'll also probably find that different tom sizes are employed between the two styles and the snares will most likely be tuned very differently or differ in size as well.
As for between songs...
I don't believe this is very widespread practice, particularly if the album has a particular aesthetic. Most albums just have the same drum sound throughout, particularly pop/rock. Then again, a diverse album that covers many different sounds is more likely to have entirely different drummers and kits for each song, depending on the production, than the same kit retuned for each song. Not saying it's never been done. I'm just saying it's not done often. I'll tell you why.
To me, drums are not like guitars. You can't tune a drum to Nashville tuning like you can a guitar. Drums are atonal but have an ambiguous fundamental "note" followed by complex overtones. I don't know if they still do it, but DW used to put a specific note inside their drums that you were supposed to tune it to. This, I presume, was the note at which the drum best resonated. If you've ever tuned drums extensively, you'll know that drums go through "tuning ranges" and most of the time only sound good in the range that is optimal for the drum's size and resonant characteristics. Remember the drum is a resonant cavity that will have some sort of fundamental when you reach the right tension. If you cause the heads to vibrate at some sort of harmonic of that fundamental, you'll get loss of tone and sustain. So to me, tuning a whole kit per song is a waste of time. So the song's in the key of D Major. Are you going to attempt to tune the toms to D F# A? You may get lucky or close, but good luck getting the drums to sing. What if the key changes every song? Good luck getting the drums to sound good in all those keys.
No, my philosophy is tune the drums until they're in the proper tuning range and sounding good and leave it at that. If there is a total shift of musicality and you need a different sound, get some different sized toms, a variety of snares to choose from or an entirely different kit. I know that's probably not easy but it's the best decision, IMO.
Cheers
