Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

One script to drop all temporary tables Expand / Collapse
Author
Message
Posted Thursday, January 16, 2014 8:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:40 AM
Points: 312, Visits: 589
I used code below to drop one temporary table. How to make code to drop all temporary tables?

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
Post #1531593
Posted Thursday, January 16, 2014 8:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 40,178, Visits: 36,581
Repeat that code for each temporary table. Or just close the connection


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1531599
Posted Thursday, January 16, 2014 10:53 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 4,400, Visits: 6,259
Why do you want a script to do this? Temp objects are automatically "managed" by the engine. Just close the connection and you don't have to worry about them. IIRC explicit drops aren't even honored any longer...

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1531709
Posted Thursday, January 16, 2014 10:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 4:43 AM
Points: 5,401, Visits: 7,512
TheSQLGuru (1/16/2014)
Why do you want a script to do this? Temp objects are automatically "managed" by the engine. Just close the connection and you don't have to worry about them. IIRC explicit drops aren't even honored any longer...


Unless that changed for 2014, they are. The #temp drop functions for batch code. IE: Strip the query out of its procedure and put that on top so you can rerun while doing performance testing/data validation.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1531713
Posted Thursday, January 16, 2014 11:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 40,178, Visits: 36,581
TheSQLGuru (1/16/2014)
IIRC explicit drops aren't even honored any longer...


They are, they always have been. Drop a temp table and you can't access it any longer. Essential when testing code before putting into a procedure. 'cannot create #test because it already exists' is annoying.

Now, the engine may choose to cache that temp table (strip out the metadata and just leave a couple of allocated pages), but it can do that with automatic drops on session close anyway.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1531719
Posted Thursday, January 16, 2014 12:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:40 AM
Points: 312, Visits: 589
Because I need test scripts in which there are a lot of temp tables. I need to modify codes or parameters to run more thank one time. If I do not delete temp tables, errors will occur.
Post #1531753
Posted Thursday, January 16, 2014 12:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 4:43 AM
Points: 5,401, Visits: 7,512
adonetok (1/16/2014)
Because I need test scripts in which there are a lot of temp tables. I need to modify codes or parameters to run more thank one time. If I do not delete temp tables, errors will occur.


Unfortunately, there's no bulk code for it.

Best thing you can do is just search the proc for CREATE TABLE # and then setup a bunch of if exists/drop at the top of the script.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1531768
Posted Thursday, January 16, 2014 5:54 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 4,400, Visits: 6,259
GilaMonster (1/16/2014)
TheSQLGuru (1/16/2014)
IIRC explicit drops aren't even honored any longer...


They are, they always have been. Drop a temp table and you can't access it any longer. Essential when testing code before putting into a procedure. 'cannot create #test because it already exists' is annoying.

Now, the engine may choose to cache that temp table (strip out the metadata and just leave a couple of allocated pages), but it can do that with automatic drops on session close anyway.




The caching is what I meant - the object "stays around" in tempdb, although yes as you say you can't actually access it any longer.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1531882
Posted Thursday, January 16, 2014 6:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
I'm curious if there's any danger to doing something like this:

DECLARE @SQL VARCHAR(MAX) =
(
SELECT 'DROP TABLE ' + STUFF(
(
SELECT ',' + name
FROM tempdb.sys.tables
FOR XML PATH('')
),1, 1, '')
);

PRINT @SQL;
--EXEC sp_executesql @SQL;


With the last statement uncommented of course.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1531889
Posted Thursday, January 16, 2014 7:01 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 4,400, Visits: 6,259
Not enough time to check, but does tempdb.sys.tables show temp objects that aren't yours? Also, does it provide a name that is "droppable"?


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1531893
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse