Error in checking table.

  • Hello experts,

    I’m trying to check for a temporary table and if it exists then delete and re-create it. I’ve this piece of code which check if table exist then delete it

    IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='#HoldDate')

    DROP TABLE #HoldDate

    However after this code when I try to create this table I end up having this error

    Msg 2714, Level 16, State 6, Line 4

    There is already an object named '#HoldDate' in the database.

    I believe something is wrong with my syntax not sure what is it. Can please somebody point out my mistake and tell me now to accomplish this task?

    Thanks a lot in advance.

  • If you look in sys.tables, you'll see that the table name is not #HoldDate. It's actually #HoldDate__________________________________<checksum here>. However you cannot just check for name like '#HoldDate%' as that'll get tables used by other connections

    I believe the way to do this is to this is to check the object_id.

    IF (SELECT OBJECT_ID('Tempdb.dbo.#HoldDate')) IS NOT NULL

    DROP TABLE #HoldDate

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I agree with Gail also will mention that the INFORMATION_SCHEMA.TABLES view is database specific so unless you run that query in tempdb you won't find your temporary table any way. Object_ID() is the way to go.

  • Thanks Gail/Jack for your inputs. So where should my CREATE TABLE code will go

    Create table #HoldDate(

    DateStamp varchar(10))

    ? After Drop table? Outside BEGIN END or inside?

  • Well, think about it logically.

    If you put the create table before the drop table, then you'll be dropping the table that you just created.

    If you put the create inside the if, you'll only create it if it existed before the proc started. If the table wasn't there earlier, you won't create it and anything the uses the table will fail

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Got it.

    IF OBJECT_ID('tempdb.dbo.#HoldDate') IS NOT NULL

    BEGIN

    DROP TABLE #HoldData;

    END

    GO

    Create table #HoldDate(DateStamp varchar(10))

    Go

    Thanks Gail.

  • The GO is not necessary and, if this is for a stored proc, shouldn't be used.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

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