Post reply

Need a RELIABLE table-exist test

  • April 29, 2005 at 9:32 pm

    #556113

    If you truly need to use positive logic (IS NULL instead of IS NOT NULL) and you also want to check for a zero value, the NULLIF function does a nice job...

    IF NULLIF(OBJECT_ID('TempDB..##tablename'),0) IS NULL

    BEGIN

    yada...yada...

    END

    Checking for the temporary table name in SYSOBJECTS of the current database won't work because temp tables live in 'TempDB'.

    Checking for the temporary table name in SYSOBJECTS of the TempDB database won't work because temp table names actually consist of the temp table name and a unique ID separated by enough underscores to make the whole temp table name 128 bytes long.  And, the unique ID is not easy to predict and is not worth the time to do so.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply