temp tables...

Question

temp tables...

dnf999

SSC Eights!

Points: 870
More actions
March 6, 2007 at 5:01 am

#194361

Hi when you create temp tables.
i.e #Table_test
does this use a lot of storage capacity in SQL server?
Also, when you search sysobjects you cannot see these temp tables, any reason why?
I want a script to delete all temp tables in my query window, but I cannot indentify them,.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

Like table variables and some views, temp tables use memory and spill over into temp db disk space if they get too big. It's a good idea to have a substantially sized TempDB on an enterprise server. Ours is 9 Gig.

Please study the content of the following URL especially Q3/A3 and Q4/A4...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

The reason why you cannot see temp tables in SysObjectsis because you are propably looking at the wrong SysObjects... Try this...

SELECT * FROM TempDB..SysObjects.

The reason why multiple people can use the same name for a temp table is because temp table names are suffixed with a wad of underscores followed by an ever increasing number. The look something like this...

#MyTemp______________________________________________________a7ba3f5d

To check if your session has a temp table in existance already and drop it...

IF OBJECT_ID('TempDB..#MyTemp') IS NOT NULL

DROP TABLE #MyTemp

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

This code only works correctly if the temporary object is a table:

IF OBJECT_ID('TempDB..#MyTemp') IS NOT NULL
DROP TABLE #MyTemp

This check makes sure it is a table:

if exists (
 select  *
 from
  tempdb.dbo.sysobjects o
 where
 o.xtype in ('U') and
  o.id = object_id( N'tempdb..#MyTemp' ) )
 DROP TABLE #MyTemp

Code available as a function, F_TEMP_TABLE_EXISTS, here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67736

dnf999 SSC Eights! Points: 870 More actions · Answer 3

Thanks that's great help. I'm trying to write a cursor to drop all my temp tables but as the table name is like #<Tablename>____________________________________________________000000258

it will not drop. Any idea's how i can tweak my cursor to drop these tables? Thanks!!

Declare @TempTable nvarchar(200),

@sqlstring nvarchar(1000)

Declare Temp_Cursor Cursor FOR

SELECT Name FROM TempDB..SysObjects where name like '#%'

OPEN Temp_Cursor

FETCH NEXT FROM Temp_Cursor Into @TempTable

WHILE (@@FETCH_STATUS = 0)

BEGIN

set @sqlstring = 'drop table ' + @Temptable

exec @sqlstring

FETCH NEXT FROM Temp_Cursor INTO @TempTable

END

CLOSE Temp_Cursor

DEALLOCATE Temp_Cursor

Error:

Msg 203, Level 16, State 2, Line 14

The name 'drop table #dnf_test___________________________________________________________________________________________________________000000000028' is not a valid identifier.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Why would you want to do that? Those tables will be automatically destroyed once the connection closes (or gets reassinged in the connection spool).

Unless you have a very serious problem on your hands I see no reasons to do this (specially since it can break some code, or more sirously give false results).

dnf999 SSC Eights! Points: 870 More actions · Answer 5

Well to be honest I don't need to drop them. I'm just being a bit anal when users are creating temp tables...

Still, I always try and like my code to work, hence my posting on if anybody knows how to tweak it.

Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

You can't drop a temp table from someone else connection like that.

The only options you have all have one thing in common : kill all active connections on the server. That's why I said do that only if you have a major crisis on your hands.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

You will not be able to do what you want, because they are not your tables to drop.

There is no good reason to do what you are trying.

dnf999 SSC Eights! Points: 870 More actions · Answer 8

Well I just want to incoporate it into a procedure so that the temp tables are dropped to ensure that there is enough space in the tempdb.

I deal with basic users and the only reason why any temp tables would have been produced are because my SP's have created them. Therefore I know that when the SP has finished running the temp tables can be dropped.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Well that is another problem altogether. You need to figure out the normal max required size of temdb at any given time of the week. Then you need to make sure that the db can grow to that size (I'd go to n times that size just to be 100% safe, you'll also have to figure out what n value makes sens in your environement). Also one good practice is to put tempdb on its own disk array (improves performance).

Maybe one step further would be to resize tempdb to its minimum required size when the server boots. However I've never heard of anyone doing this so I'm not even sure if it is possible.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

It is OK to drop a temp table created in a stored procedure, and I posted code that shows you how to do this.

There is no good reason to drop temp tables created on another connection, and could cause serious problems if you could. The temp tables will go away anyway when the connection that created them ends.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Just to complete... The temp table will also be dropped when the sp in question ends, or when the table is explicitly dropped in the sp.

Again no need to drop the table in other connections unless you have some major problem I can't even imagine right now.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

Emphasis... when a session is completed, the Temp Tables are automatically dropped. I don't believe there's a way for you to loop through TempDB to figure out which are "yours".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

No you can only check if the table exists in tempdb..#tablename to see if you own it. Not much else you can do, and for good reasons.