Temp Table List

Question

Temp Table List

texpic

SSCertifiable

Points: 5885
More actions
March 24, 2008 at 10:30 pm

#124607

Is there a stored procedure anyone has that will list all the active temp tables being used by all the databases in SQL Server?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

This will get it for you. I don't know if there's a way to tie the name back to the creating SPID though. don't think so.

SELECT * FROM tempdb..sysobjects WHERE NAME LIKE '#%' AND xtype = 'U'

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Gail

One doubt... This query returned tables with names like '#042A2C58' , '#0626B80C' etc. Iam sure these temp tables have not been created by the developers or any other users. Nor are they created inside any procs.

"Keep Trying"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Those are table variables. Unlike temp tables, the name that they are given in code has no relation to the name assigned in TempDB.

It's also possible that temp tables created by various system processes appears in the list. I'm not sure if there's any way to find the process that created a temp object, and thereby filter out ones created by the system.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

ChiragNS

One Orange Chip

Points: 26137

March 27, 2008 at 4:36 am

#794502

Thanks Gail. :).

"Keep Trying"

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

just for fun:

select name

from tempdb..sysobjects

where name like '#%'--> table variables

and name not like '#[A-Z,a-z]%' --> temp tables

select name

from tempdb..sysobjects

where name like '#[A-Z,a-z]%'--> temp tables

select name

from tempdb..sysobjects

where name like '#%'--> temp tables and table variables

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

I just ran into an old article - it seems to be the 'holy grail' (or at least a piece of it) regarding tebp table creation ...

https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."