SQL 删除实例的所有临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5497450/
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
Drop all temporary tables for an instance
提问by Dibstar
I was wondering how / if it's possible to have a query which drops all temporary tables?
我想知道如何/是否可以进行删除所有临时表的查询？
I've been trying to work something out using the tempdb.sys.tables, but am struggling to format the name column to make it something that can then be dropped - another factor making things a bit trickier is that often the temp table names contain a '_' which means doing a replace becomes a bit more fiddly (for me at least!)
我一直在尝试使用 tempdb.sys.tables 解决一些问题，但是我正在努力格式化名称列以使其成为可以删除的内容 - 另一个使事情变得有点棘手的因素是临时表名称通常包含'_' 意味着进行替换变得更加繁琐（至少对我来说！）
Is there anything I can use that will drop all temp tables (local or global) without having to drop them all individually on a named basis?
有什么我可以使用的东西可以删除所有临时表（本地或全局），而不必在命名的基础上单独删除它们？
Thanks!
谢谢！
回答by RichardTheKiwi
The point of temporary tables is that they are.. temporary. As soon as they go out of scope
临时表的要点是它们是.. 临时的。一旦它们超出范围
- #temp create in stored proc : stored proc exits
- #temp created in session : session disconnects
- ##temp : session that created it disconnects
- #temp 在存储过程中创建：存储过程退出
- #temp 在会话中创建：会话断开连接
- ##temp ：创建它的会话断开连接
The query disappears. If you find that you need to remove temporary tables manually, you need to revisit how you are using them.
查询消失。如果您发现需要手动删除临时表，则需要重新审视如何使用它们。
For the global ones, this will generate and execute the statement to drop them all.
对于全局的，这将生成并执行语句以将它们全部删除。
declare @sql nvarchar(max)
select @sql = isnull(@sql+';', '') + 'drop table ' + quotename(name)
from tempdb..sysobjects
where name like '##%'
exec (@sql)
It is a bad idea to drop other sessions' [global] temp tables though.
不过，删除其他会话的 [全局] 临时表是一个坏主意。
For the local (to this session) temp tables, just disconnect and reconnect again.
对于本地（到此会话）临时表，只需断开连接并再次重新连接。
回答by dsz
The version below avoids all of the hassles of dealing with the '_'s. I just wanted to get rid of non-global temp tables, hence the '#[^#]%'in my WHEREclause, drop the [^#]if you want to drop global temp tables as well, or use a '##%'if you onlywant to drop global temp tables.
下面的版本避免了处理'_'s 的所有麻烦。我只是想摆脱非全局临时表，因此'#[^#]%'在我的WHERE条款，删除[^#]，如果你想删除全局临时表为好，或使用'##%'，如果你只想要放弃全局临时表。
The DROPstatement seems happy to take the full name with the '_', etc., so we don't need to manipulate and edit these. The OBJECT_ID(...) NOT NULLallows me to avoid tables that were not created by my session, presumably since these tables should not be 'visible' to me, they come back with NULL from this call. The QUOTENAMEis needed to make sure the name is correctly quoted / escaped. If you have no temp tables, @d_sqlwill be the empty string still, so we check for that before printing / executing.
该DROP语句似乎很乐意使用 等的全名'_'，因此我们不需要操纵和编辑这些。这OBJECT_ID(...) NOT NULL允许我避免不是由我的会话创建的表，大概是因为这些表对我来说不应该是“可见的”，他们从这次调用中返回 NULL。的QUOTENAME需要，以确保名称正确引用/逃脱。如果您没有临时表，@d_sql则仍然是空字符串，因此我们在打印/执行之前检查它。
DECLARE @d_sql NVARCHAR(MAX)
SET @d_sql = ''
SELECT @d_sql = @d_sql + 'DROP TABLE ' + QUOTENAME(name) + ';
'
FROM tempdb..sysobjects
WHERE name like '#[^#]%'
AND OBJECT_ID('tempdb..'+QUOTENAME(name)) IS NOT NULL
IF @d_sql <> ''
BEGIN
PRINT @d_sql
-- EXEC( @d_sql )
END
回答by Martin Smith
In a stored procedure they are dropped automatically when the execution of the proc completes.
在存储过程中，当 proc 执行完成时，它们会自动删除。
I normally come across the desire for this when I copy code out of a stored procedure to debug part of it and the stored proc does not contain the drop tablecommands.
当我从存储过程中复制代码以调试其中的一部分并且存储过程不包含drop table命令时，我通常会遇到这种需求。
Closing and reopening the connection works as stated in the accepted answer. Rather than doing this manually after each execution you can enable SQLCMD mode on the Query menu in SSMS
关闭和重新打开连接的工作方式如已接受的答案中所述。您可以在 SSMS 的“查询”菜单上启用 SQLCMD 模式，而不是在每次执行后手动执行此操作
And then use the :connectcommand (adjust to your server/instance name)
然后使用:connect命令（调整到您的服务器/实例名称）
:connect (local)\SQL2014
create table #foo(x int)
create table #bar(x int)
select *
from #foo
Can be run multiple times without problems. The messages tab shows
可以多次运行没有问题。消息选项卡显示
Connecting to (local)\SQL2014...
(0 row(s) affected)
Disconnecting connection from (local)\SQL2014...
正在连接到（本地）\SQL2014...
(0 行受影响)
正在断开与（本地）\SQL2014 的连接...
