Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Your profile

Search

January 2009
Mon Tue Wed Thu Fri Sat Sun
 << <   > >>
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31  

XML Feeds

Authors

« SQL Server 2008 Express with Advanced Services And SQL Server 2008 Express With Tools Now Available For DownloadOnly In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code »
The Data Management Journal

How Do You Check If A Temporary Table Exists In SQL Server

by SQLDenis


Permalink 22 Aug 2008 08:57 , Categories: Data Modelling & Design Tags: howto, programming, sql server, t-sql, tip, trick

I see more and more people asking how to check if a temporary table exists. How do you check if a temp table exists?

You can use IF OBJECT_ID('tempdb..#temp') IS NOT NULL Let's see how it works

  1. --Create table
  2. USE Norhtwind
  3. GO
  4.  
  5. CREATE TABLE #temp(id INT)
  6.  
  7. --Check if it exists
  8. IF OBJECT_ID('tempdb..#temp') IS NOT NULL
  9. BEGIN
  10. PRINT '#temp exists!'
  11. END
  12. ELSE
  13. BEGIN
  14. PRINT '#temp does not exist!'
  15. END
  16.  
  17. --Another way to check with an undocumented optional second parameter
  18. IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
  19. BEGIN
  20. PRINT '#temp exists!'
  21. END
  22. ELSE
  23. BEGIN
  24. PRINT '#temp does not exist!'
  25. END
  26.  
  27.  
  28.  
  29. --Don't do this because this checks the local DB and will return does not exist
  30. IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
  31. BEGIN
  32. PRINT '#temp exists!'
  33. END
  34. ELSE
  35. BEGIN
  36. PRINT '#temp does not exist!'
  37. END
  38.  
  39.  
  40. --unless you do something like this
  41. USE tempdb
  42. GO
  43.  
  44. --Now it exists again
  45. IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
  46. BEGIN
  47. PRINT '#temp exists!'
  48. END
  49. ELSE
  50. BEGIN
  51. PRINT '#temp does not exist!'
  52. END
  53.  
  54. --let's go back to Norhtwind again
  55. USE Norhtwind
  56. GO
  57.  
  58.  
  59. --Check if it exists
  60. IF OBJECT_ID('tempdb..#temp') IS NOT NULL
  61. BEGIN
  62. PRINT '#temp exists!'
  63. END
  64. ELSE
  65. BEGIN
  66. PRINT '#temp does not exist!'
  67. END

now open a new window from Query Analyzer (CTRL + N) and run this code again

  1. --Check if it exists
  2. IF OBJECT_ID('tempdb..#temp') IS NOT NULL
  3. BEGIN
  4. PRINT '#temp exists!'
  5. END
  6. ELSE
  7. BEGIN
  8. PRINT '#temp does not exist!'
  9. END

It doesn't exist and that is correct since it's a local temp table not a global temp table

Well let's test that statement

  1. --create a global temp table
  2. CREATE TABLE ##temp(id INT) --Notice the 2 pound signs, that's how you create a global variable
  3.  
  4. --Check if it exists
  5. IF OBJECT_ID('tempdb..##temp') IS NOT NULL
  6. BEGIN
  7. PRINT '##temp exists!'
  8. END
  9. ELSE
  10. BEGIN
  11. PRINT '##temp does not exist!'
  12. END

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

  1. --Check if it exists
  2. IF OBJECT_ID('tempdb..##temp') IS NOT NULL
  3. BEGIN
  4. PRINT '##temp exists!'
  5. END
  6. ELSE
  7. BEGIN
  8. PRINT '##temp does not exist!'
  9. END

And yes this time it does exist since it's a global table

I have also added this to our wiki, read it here: Check If Temporary Table Exists

Leave a comment »Send a trackback » 1537 views

Trackback address for this post

Trackback URL (right click and copy shortcut/link location)

No feedback yet

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
PoorExcellent
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)