Posted By

hairajeshk on 09/15/10


Tagged

table temp


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Tyster


Check temporary table exists


 / Published in: SQL
 

Verify whether temporary table created is exists or not

  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

Report this snippet  

You need to login to post a comment.