1

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.

Improve this question
2
  • 1
    What is wrong with writing code to remove them? What have you tried that did not work? Have you read the docs on how temp tables work?
    – LowlyDBA - John M
    Commented Nov 6, 2018 at 16:40
  • I have made multiple temp tables and I am manipulating them at the end to create a permanent table. Removing them earlier in the script will not allow me to use them at the end. Also, I was experimenting a lot with the query so I kept modifying the temp table names and so I have ended up with temp tables whose name I don't even remember. That's why I am looking to find a solution like clear console which allows to remove them completely and run my script with the correct names.
    – T.H.
    Commented Nov 6, 2018 at 16:47

1 Answer 1

Reset to default
5

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.

Improve this answer
2
  • Thanks for this. How do you think I can make sure that I have deleted all temp tables because I don't even remember some of the names?
    – T.H.
    Commented Nov 6, 2018 at 16:54
  • 3
    You don't have to worry about that... once the session that created them is over, they will be dropped. If you are leaving all of your sessions open, then you have larger issues. More on temporary tables but you can list them all by looking in tempdb.sys.objects
    – S3S
    Commented Nov 6, 2018 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.