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

Temp Table List Expand / Collapse
Author
Message
Posted Monday, March 24, 2008 10:30 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:06 PM
Points: 235, Visits: 320
Is there a stored procedure anyone has that will list all the active temp tables being used by all the databases in SQL Server?



Post #473860
Posted Tuesday, March 25, 2008 12:06 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: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
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 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 #473877
Posted Wednesday, March 26, 2008 11:03 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
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"
Post #475220
Posted Wednesday, March 26, 2008 11:24 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 @ 9:44 AM
Points: 42,822, Visits: 35,953
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 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 #475225
Posted Thursday, March 27, 2008 4:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
Thanks Gail. :).



"Keep Trying"
Post #475301
Posted Thursday, June 12, 2014 4:16 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: Yesterday @ 4:38 PM
Points: 3,214, Visits: 2,335
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





Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #1580293
Posted Friday, June 13, 2014 8:26 AM
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: Yesterday @ 4:38 PM
Points: 3,214, Visits: 2,335
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/




Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #1580551
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse