Sorry. I can help thinking about that. Auditing the content of temp tables. If someone insisted that I make that a priority at work and insisted that it be done or be fired, I'd walk out without further invitation. I just don't see the utility in doing such a thing.

This whole thread is a bit odd to me. I've been trying to convince folks over the years that using Temp Tables to Divide'n'Conquer large queries is the way to go because the large queries are going to be hammering TempDB with hash joins and work tables for index spools and accidental cross joins if you do nothing about them, especially the way some folks write them.

I'll stand by my original suggestion and that's not to worry so much about what's using TempDB. The time spent finding and ironing out poor performing and resource hungry code will return a much better ROI than searching the TempDB closet for mothy clothes, which will boil to the top of the list anyway if they are, indeed, poor performing or resource hungry moths. 😉