Error in checking table.

Question

Error in checking table.

mr_adeelalisyed

SSC-Addicted

Points: 470
More actions
December 15, 2009 at 7:36 am

#385947

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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mr_adeelalisyed SSC-Addicted Points: 470 More actions · Answer 3

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

mr_adeelalisyed SSC-Addicted Points: 470 More actions · Answer 5

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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