Temp DB

Question

Hi guys ..

we have one stored procedure running through front end application . That procedure creates temp table (#) but in procedure there is no DROP command for temp table at the end. So what would be happening with that temp table ?

That temp table resides always in tempdb or automatically dropped after SP finish execution?

Thanks...

---

Ankit Shah SQL Server DBA

Answer 1

Temp Table will be destored by SQL Server engine automatically after the connection that created temp table closes.

So temp table exists as long as connection that created temp table exists, as connection that created temp table get destoryed so does the temp table. 

Yes, temp table exists in TempDB and it will be automatically dropped after SP finishes execution.