[sql] 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.

This question is related to sql sql-server sql-server-2000 temp-tables

The answer is


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

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:

[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.


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.


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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2000

SQL get the last date time record INSERT INTO @TABLE EXEC @query with SQL Server 2000 How to get the first and last date of the current year? SQL select max(date) and corresponding value SQL where datetime column equals today's date? Is there a way to get a list of all current temporary tables in SQL Server? Grant execute permission for a user on all stored procedures in database? Is there a way to list open transactions on SQL Server 2000 database? Get the time of a datetime using T-SQL? select a value where it doesn't exist in another table

Examples related to temp-tables

How to find difference between two columns data? Temporary table in SQL server causing ' There is already an object named' error SQL Server Creating a temp table for this query Create a temporary table in MySQL with an index from a select How can I simulate an array variable in MySQL? When should I use a table variable vs temporary table in sql server? Inserting data into a temporary table TSQL select into Temp table from dynamic sql dropping a global temporary table Is there a way to get a list of all current temporary tables in SQL Server?