删除实例的所有临时表 [英] Drop all temporary tables for an instance

问题描述

我想知道如何/是否可以进行删除所有临时表的查询?

I was wondering how / if it's possible to have a query which drops all temporary tables?

我一直在尝试使用 tempdb.sys.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!)

我可以使用什么来删除所有临时表(本地或全局)，而不必在命名的基础上单独删除它们?

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?

谢谢！

推荐答案

临时表的要点在于它们是..临时的.一旦超出范围

The point of temporary tables is that they are.. temporary. As soon as they go out of scope

• #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.

这篇关于删除实例的所有临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！