有没有办法获取 SQL Server 中所有当前临时表的列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7075483/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to get a list of all current temporary tables in SQL Server?
提问by AAsk
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.
感谢您的指导。
回答by Sandro
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
回答by Upendra Chaudhari
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
回答by FLICKER
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.
如果要包含全局临时表，可以删除 ## 行。
回答by Aaron Bertrand
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 版本。）这假设您没有使用三个连续的下划线命名您的表，例如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));
回答by Mitch Wheat
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 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.
如果您有一个对您的业务很重要的冗长流程，最好在流程中记录各个步骤（步骤名称/编号、开始和结束时间）。这样，当事情表现不佳时，您就有了一个基准来进行比较，并且您可以更快地查明哪些步骤导致了问题。
