在SQL Server中，有没有一种方法可以获取所有当前临时表的列表？

Question

我意识到临时表是与会话/连接绑定的，不能在会话/连接之外被看到或访问。

我有一个长时间运行的存储过程，在各个阶段创建临时表。

有没有办法查看当前临时表的列表？我需要哪些权限才能这样做？

或者，

有没有办法在运行的存储过程中查看正在执行的特定SQL语句？该过程作为SQL Server中的计划作业运行。

我正在使用SQL Server 2000.

感谢您的指导。

- AAsk

Answer 1

这是你想要的吗？

select * from tempdb..sysobjects
--for sql-server 2000 and later versions

select * from tempdb.sys.objects
--for sql-server 2005 and later versions

- Sandro

---

这在SQL Server 2000中失败了。 - AAsk

2008年：tempdb.sys.objects，2000年：tempdb..sysobjects。 - Mitch Wheat

这里列举了许多Tempdb中的不同对象，包括索引和全局临时表。不应选择作为答案。 - FLICKER

Answer 2

您可以通过以下查询获取临时表列表：

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

- Upendra Chaudhari

---

只是一条注释，我认为你最后一个从句更易读，应该写成 and object_id(...) 不为空。 - Aaron Bertrand

这也会返回永久表（显然可能不属于我的会话）。 - Aaron Bertrand

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

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

- FLICKER

Answer 4

对于SQL Server 2000，这将只告诉您会话中的#temp表。 (改编自我在此处提供的较新版本的SQL Server示例。) 这假定您没有使用三个连续下划线命名表，例如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));

- Aaron Bertrand

Answer 5

如果您需要“查看”临时表列表，可以简单地记录使用的名称。（正如其他人所指出的那样，直接查询此信息是可能的）

如果您需要“查看”临时表的内容，则需要创建具有（唯一）临时名称的实际表。

您可以使用SQL Profiler跟踪执行的SQL：

- 使用SQL Server Profiler - 如何使用SQL Profiler识别运行缓慢的查询

[这些文章针对的是2000年之后的SQL Server版本，但大部分建议都相同。]

如果您有一个重要的长时间过程，最好记录过程中的各个步骤（步骤名称/编号、开始和结束时间）。这样，当事情不顺利时，您就有一个基准来进行比较，并且可以更快地确定哪些步骤导致问题。

- Mitch Wheat

---

谢谢。 “记录使用的名称”是指将临时表的名称（创建后）写入另一个表吗？ - AAsk

@AAsk：是的，那是一个可能性。我可以问一下你实际上想解决什么问题吗？你有很多临时表吗？ - Mitch Wheat

我有一个存储过程，每周日凌晨3点运行一次，通常需要不到2小时。上个星期天它持续了6个小时还没有完成。我将再次运行它，没有任何修改 - 因此我的问题。如果它再次失败，我将在关键阶段向“调试”表写入消息，以便查看其进度。 - AAsk

除了“具有（唯一）临时名称的实际表”之外，您还可以考虑“具有SPID/session_id键的单个实际表”... - Aaron Bertrand