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.

Answer 1

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 

Answer 2

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