当前连接上的临时表 [英] Temp tables on the current connection

问题描述

如果我这样做:

select * from tempdb.sys.tables

我会看到系统中的所有临时表，但是该视图没有关于每个表属于哪个连接/用户的信息.我只想找到我在当前连接上创建的表.有没有办法做到这一点?

I will see all the temporary tables in the system, however that view does not have information about which connection/user each table belongs to. I'm interested in finding only the tables I've created on my current connection. Is there a way to do this?

谢谢 - e

附言是的，我可以尝试阅读列出的每个表，并认为那些成功的表应该证明是我的(在最近的版本中，不能读取其他连接的表)但是这种方法成本太高，因为可能有数千个表系统

p.s. yes, I could try reading each table listed with the notion that those that succeed should prove to be mine (on recent versions one can't read other connections' tables) but that is too costly an approach since there may be thousands of tables on the system

p.p.s.我确实读过 有没有办法获得 SQL Server 中所有当前临时表的列表? 提出了正确的问题但没有得到好的答案

p.p.s. I did read Is there a way to get a list of all current temporary tables in SQL Server? which asks the right question but did not get a good answer

推荐答案

假设你没有用三个连续的下划线命名你的#temp 表，这应该只选择你的#temp 表.但是，它不会获取您的表变量，您也不能以某种方式更改此代码以选择其他人连接上的表 - 这仅适用于 OBJECT_ID('tempdb..#foo')只能为会话中的表返回 true.

Assuming you don't name your #temp tables with three consecutive underscores, this should only pick up your #temp tables. It won't, however, pick up your table variables, nor can you change this code somehow to pick the tables on someone else's connection - this only works because OBJECT_ID('tempdb..#foo') can only return true for a table in your session.

SELECT 
  name = SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1),
  t.[object_id]
FROM tempdb.sys.tables AS t
WHERE t.name LIKE '#%[_][_][_]%'
AND t.[object_id] = 
  OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1));

您可能还对这些表(至少对于堆或聚集索引)使用的空间感兴趣，例如:

You might also be interested in space used by each of these tables (at least for the heap or clustered index), e.g.:

SELECT 
    name = SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1),
    t.[object_id], 
    p.used_page_count, 
    p.row_count
FROM tempdb.sys.tables AS t
INNER JOIN tempdb.sys.dm_db_partition_stats AS p
ON t.[object_id] = p.[object_id]
WHERE t.name LIKE '#%[_][_][_]%'
AND p.index_id IN (0,1)
AND t.[object_id] = 
    OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1));

您可以扩展它以显示所有索引的总空间.因为这些是#temp 表，所以我没有打扰每个分区的聚合.

You could extend that to show total space for all indexes. I didn't bother aggregating per partition since these are #temp tables.

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