January 16, 2014 at 8:02 am
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
January 16, 2014 at 8:09 am
Repeat that code for each temporary table. Or just close the connection
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
January 16, 2014 at 10:53 am
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 on googles mail service
January 16, 2014 at 10:55 am
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 16, 2014 at 11:03 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2014 at 12:00 pm
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.
January 16, 2014 at 12:42 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 16, 2014 at 5:54 pm
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 on googles mail service
January 16, 2014 at 6:17 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 16, 2014 at 7:01 pm
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 on googles mail service
January 16, 2014 at 10:21 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2014 at 10:23 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2014 at 8:50 am
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.
February 18, 2014 at 10:39 pm
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply