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 10:21 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 40,596, Visits: 37,053
TheSQLGuru (1/16/2014)
Not enough time to check, but does tempdb.sys.tables show temp objects that aren't yours?


Yes

Also, does it provide a name that is "droppable"?


No, though you could probably write code that gets it right most of the time.



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 #1531921
Posted Thursday, January 16, 2014 10:23 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 40,596, Visits: 37,053
TheSQLGuru (1/16/2014)
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.


The 'object' without it's name, without most of its metadata and without all but two (I think) pages. It's pretty much a drop, what SQL chooses to do behind the scenes is up to it, also it won't always be cached.



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 #1531923
Posted Monday, February 17, 2014 8:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 5:12 AM
Points: 448, Visits: 1,333
This works, unless you use #temp table names with more than two subsequent underscores or ending with an underscore:

CREATE PROCEDURE [dbo].[dtt]
AS
-- drop all #temp tables for current session
begin
DECLARE @sql VARCHAR(60),
@name VARCHAR(60)

DECLARE dtt CURSOR
FOR SELECT SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1)
FROM tempdb.sys.tables AS t
WHERE t.name LIKE '#%[_][_][_]%'
AND t.[object_id] = OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1))
open dtt
fetch next from dtt into @name
while @@fetch_status <> -1
BEGIN
SELECT @sql = 'DROP TABLE ' + @name

EXEC ( @sql )

fetch next from dtt into @name
END
CLOSE dtt
deallocate dtt
END

It works because OBJECT_ID returns NULL for #temp tables that belong to other sessions.
Post #1542174
Posted Tuesday, February 18, 2014 10:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:33 PM
Points: 102, Visits: 80
TheSQLGuru (1/16/2014)
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"?


Yes, the name that is in tempdb.sys.tables is "droppable" but yes it will also have all the additional characters like at the end of the table name "________________________________________________________________________________________________________________0000000E300B" at the end. Just in case you end up with some stupid naming conventions of temp tables in your script you can use the following just to be safe:

DECLARE @sql NVARCHAR(MAX) = N''
SELECT
@sql = @sql + CHAR(10) + N'DROP TABLE ' + QUOTENAME([TABLE_SCHEMA]) + '.' + QUOTENAME([TABLE_NAME])
FROM [tempdb].[INFORMATION_SCHEMA].[TABLES]
PRINT @sql
EXEC sp_executesql @sql

Though you cannot recreate a table in the tempdb with the same name as a table created earlier in the same batch. For example the following code will cause an error if run in a single batch, but would execute normally if run in separate batches or even if the tables were not created in tempdb:

SELECT
1 AS [var]
INTO #tab

DROP TABLE [#tab]

SELECT
1 AS [var]
INTO #tab

Post #1542864
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse