Is there a way to get a list of all current temporary tables in SQL Server

Question

I realize that temporary tables are session connection bound and not visible or accessible out of the session connection   I have a long running stored procedure that creates temporary tables at various stages   Is there a way I can see the list of current temporary tables  What privileges do I need to be able to do so   Alternatively   Is there a way I can see the particular SQL statement being executed inside a running stored procedure  The procedure is running as a scheduled job in SQL Server   I am using SQL Server 2000   Thanks for your guidance

User · Accepted Answer

Is this what you are after   select   from tempdb  sysobjects --for sql-server 2000 and later versions  select   from tempdb sys objects --for sql-server 2005 and later versions

User · Answer

SELECT left NAME  charindex      NAME  - 1  FROM tempdb  sysobjects WHERE NAME LIKE          AND NAME NOT LIKE           AND upper xtype     U      AND NOT object id  tempdb      NAME  IS NULL   you can remove the    line if you want to include global temp tables

User · Answer

You can get list of temp tables by following query    select left name  charindex     name -1   from tempdb  sysobjects where charindex     name   gt  0 and xtype    u  and not object id  tempdb    name  is null

User · Answer

For SQL Server 2000  this should tell you only the  temp tables in your session   Adapted from my example for more modern versions of SQL Server here   This assumes you don t name your tables with three consecutive underscores  like CREATE TABLE  foo   bar   SELECT    name   SUBSTRING t name  1  CHARINDEX        t name -1     t id FROM tempdb  sysobjects AS t WHERE t name LIKE                AND t id      OBJECT ID  tempdb      SUBSTRING t name  1  CHARINDEX        t name -1

User · Answer

If you need to  see  the list of temporary tables  you could simply log the names used   and as others have noted  it is possible to directly query this information   If you need to  see  the content of temporary tables  you will need to create real tables with a  unique  temporary name   You can trace the SQL being executed using SQL Profiler    Using SQL Server Profiler How to Identify Slow Running Queries with SQL Profiler    These articles target SQL Server versions later than 2000  but much of the advice is the same    If you have a lengthy process that is important to your business  it s a good idea to log various steps  step name number  start and end time  in the process  That way you have a baseline to compare against when things don t perform well  and you can pinpoint which step s  are causing the problem more quickly

[sql] Is there a way to get a list of all current temporary tables in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2000

Examples related to temp-tables