How do I go about buying a two-week ticket to travel around Stuttgart and travel to Rottweil and Tubingen?
Should I buy a tourist pass? What is the best value?
Rottweil and Tuebingen are outside the Stuttgart Verkehrsverbund territory. You can use a Baden-Wuerttenberg Ticket to travel to these on day trips. Weekly Stuttgart VV tickets are priced by zone, so it depends how much of the district you want to travel. Here is the zone map.
https://download.vvs.de/Tarifzonenplan.pdf
The B-W Ticket is 27 EUR for 1 person and 9 EUR for each additional person for up to 5 traveling together. https://www.bahn.de/angebot/regio/laender-tickets/baden-wuerttemberg-ticket
You should also consider getting a Monthly Deutschland Ticket. If your stay is within a calendar month, you can cancel the monthly subscription. Cost is 63 EUR for one month and good for all local transportation and regional trains in Germany. Several ways to buy.
https://int.bahn.de/en/offers/regional/deutschland-ticket
When I priced it out (I live in the Stuttgart area, outermost zone), it was much cheaper to buy a Deutschland ticket than a month card through the local transit authority...and the Deutschland ticket lets you travel with the local public transit and regional trains all throughout Germany. Not sure how it would compare for a period of two weeks, but worth a look?
With a Deutschland Ticket you are also allowed to take IC train to Rottweil or even Konstanz. Also VVS ticket would be sufficient. One of the few exceptions where a regional ticket allows IC (long-disance) train usage.