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

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.

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

You can get list of temp tables by following query :

select left(name, charindex('_',name)-1) 
from tempdb..sysobjects
where charindex('_',name) > 0 and
xtype = 'u' and not object_id('tempdb..'+name) is null

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.

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.

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));