是否有办法在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.

是否有一种方法可以让我看到正在运行的存储过程中执行的特定SQL语句?该过程在SQL Server中作为计划作业运行。

I am using SQL Server 2000.

我正在使用SQL Server 2000。

Thanks for your guidance.

谢谢你的指导。

5 个解决方案

#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

#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

#3

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:

对于SQL Server 2000，这应该只告诉您会话中的#temp表。(根据我的示例，这里有更现代的SQL Server版本)。这假定您不使用三个连续的下划线来命名表，比如创建表#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));

#4

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:

您可以使用SQL分析器跟踪正在执行的SQL:

Using SQL Server Profiler

使用SQL Server分析器
How to Identify Slow Running Queries with SQL Profiler

如何使用SQL分析器识别运行缓慢的查询

[These articles target SQL Server versions later than 2000, but much of the advice is the same.]

[这些文章针对的是2000年以后的SQL Server版本，但大多数建议是一样的。]

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.

如果您有一个对您的业务很重要的冗长流程，那么最好在流程中记录各种步骤(步骤名/编号、开始和结束时间)。这样你就有了一个基线来比较当事情做得不好的时候，你就可以更迅速地确定是哪一步导致了问题。

#5

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.

如果您想要包含全局临时表，可以删除##行。

#1