>We had the same issue in Fox when we devised this method. In Fox we had a remedy for that. We had an object up at runtime that tracked the building of our calculated cursors ( temp tables ) and before each view ( or temp table is built that is based on other temp tables ) is run we check to see if the temp tables need updating based on any other data changing ( via the UI ). We run this process through meta data so it is quite automated. I can see us having the same method on the server. Also, several of our temp tables are just runtime copies of a static data images that does not change it is just there for us to join to for speeding up the queries. Som eof the tabales are created then destroyed once used as well to prevent collisions.
I've learned to create really long SQL scripts, where a temp table is used, reused, abused and eventually dropped. The longest one so far is over 16K of text, builds 2 or 3 #tables (depending on the request, not all parts get generated each time) and drops them before exiting. Returns anywhere between 11 and 20 cursors. Haven't tried executing a SP which would create a #temp table, but it probably would stay there for the time of the current sqlexec() call.
Internally, the table is called #temp____________________xxxxx something, where xxxxx are a bunch of hex digits and the total length of this name is 64 or 128 or some other round number.