Drop table if exists

[robertfah]

Can someone tell me the syntax to drop a temp table if it exists?

I've got this sql here:

IF EXISTS(SELECT [name] FROM tempdb..sysobjects WHERE [Name] = '#TempSummary')
BEGIN
DROP TABLE #TempSummary
END

but further down in my SP, when I try to create the table, I get an erro stating it already exists:

Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named '#TempSummary' in the database.

Any clues?

 

[AlexCuse]

Try this:

IF EXISTS(SELECT [name] FROM tempdb..sysobjects WHERE [Name] like '#TempSummary%')
    BEGIN
        DROP TABLE #TempSummary
    END

Temp tables show up in tempdb..sysobjects with a Name like this:

#tempsummary________________________________________________________________________________________________________00000006AFDF

So that is probably your problem.

Hope it helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

D'oh!

I have ruined this thread and made it too wide for IE. Anyway, after the long underscore, I see this: 00000006AFDF

This is assigned so that if you create a temp table in different databases/sessions with the same name, the system can tell them apart.

If you don't want it to do this, you can make your temp tables global (use two #'s before the name). This will make them available to all sessions though.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

Why you use #Temp Table and not TABLE VARIABLE?
Of course temp tables are useful when you need to get the results from other SP, but if I use this table only in current SP I use table variables.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP