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.

Jamin

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

Luehrmann

This fails in SQL Server 2000. – Jamin

In 2008: tempdb.sys.objects, In 2000: tempdb..sysobjects – Kerchief

this lists many different objects in tempdb including indexes and also global temp tables. should not be chosen as answer – Fixture

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

Drosophila

Just a comment, I would find your last clause much more readable as and object_id(...) is not null – Bant

This returns permanent tables as well (which obviously may not belong to my session). – Bant

Answer 3

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.

Fixture

Answer 4

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

Bant

Answer 5

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.

Kerchief

Thanks. By 'log the names used', Am I correct in thinking that you mean write the temp table name (after creation) to another table? – Jamin

@AAsk: yes, that's one possibility. Can I ask what you are actually trying to solve? Do you have many temp tables? – Kerchief

I have a stored procedure running once a week on Sunday at 3:00am, usually taking just under 2 hours. Last Sunday it carried on for 6 hours and hadn't finished. I am running it again without alteration - hence my question. If it fails again, I'll write messages at key stages to a 'debug' table so I can see its progress. – Jamin

In addition to "real tables with a (unique) temporary name" you could also consider "a single real table with a key on SPID/session_id"... – Bant