How to remove temp tables from SQL Server?

Posted on

Question :

I want to remove temp tables that I was using in my script in SQL Server 2008. I can’t find a way to remove it without writing a few lines of code. I have not been able to find code that works properly. Is there any way to do this without writing code at all? If not, what should be my approach? Will copying my code in a new script and then creating same name temp tables help?

Any help/advice/suggestion much appreciated.

Answer :

Temporary Tables are dropped when the session ends. So, they will be dropped when you close your SSMS window, or your application session ends.

If you are working in SSMS developing code (and keeping the session open) then you’ll need to drop them explicitly via…

drop #tempTable

Another common method, to prevent you from attempting to drop a Temporary Table that doesn’t exists, is to check if it exists first.

if object_id('tempdb..#tempTable') is not null
drop table #tempTable

create table #tempTable...
or
select into #tempTable

A note on some helper functions from Aaron Bertrand should be mentioned.

Leave a Reply

Your email address will not be published. Required fields are marked *