Home Forums Article Discussions Article Discussions by Author Discuss content posted by VM temp table in sysobjects RE: temp table in sysobjects

  • November 22, 2010 at 9:04 am

    #1253815

    Carlo Romagnano (11/22/2010)

    Carlo Romagnano (11/22/2010)

    WayneS (11/22/2010)

    BTW, the best way to check for the existance of a temporary table in your current session is:

    IF OBJECT_ID('tempdb..#YourTempTable') IS NOT NULL

    Note that in SQL Server thru version 2008R2, temp tables are created with a positive object_id value, so you might want to use:

    IF OBJECT_ID('tempdb..#YourTempTable') > 0

    You can test <> 0

    I always use IF OBJECT_ID('tempdb..#YourTempTable') <> 0 😀

    But as Wayne said, the best way is to use IS NOT NULL. Why? Because then you're testing for the defined behavior. OBJECT_ID() is designed to return NULL (NOT 0) for objects which do not exist or are not schema-scoped.

    So if you test against 0, you're designing potential failure (there's no technical reason why Microsoft couldn't have SQL Server create an object with an ID of 0 -- it's just another number, after all) into your application for no good reason.

    http://msdn.microsoft.com/en-us/library/ms190328.aspx