是否可以读取另一个会话的临时表中的数据? [英] Is it possible to read data in another session's temporary table?
问题描述
我们正在维护(偶尔调试)一个大型内部系统.该系统有 20 多个数据库，以及一些与其他系统接口、处理数据等的服务器.并非所有都是内部开发的 - 即我们并不总是可以访问源代码.
We're maintaining (and occasionally debugging) a large in-house system. The system has 20+ databases, and a number of servers interfacing to other systems, processing data, etc. Not all is in-house developed - i.e. we don't always have access to source code.
在某个地方，我们可以看到系统创建了一个 #temp 表 - 然后在下一步中，由于数据错误而失败.我们可以在Management Studio 中看到#temp 表的存在——它存在于tempdb -->临时表类似于
At one place, we can see the system creating a #temp table - and then, in the next step, failing due to a data-error. We can see the existence of the #temp table in Management Studio - it exists in tempdb --> Temporary Tables as something like
#MyStuff________________________________________________________________________________________________________000000A65029
显然，此处的上下文菜单不提供完整功能(包括创建表格、选择前 1000 项等)——而仅提供报告和Refresh.
Obviously, the context menu here doesn't offer the full functionality (with Create table, select top 1000, etc.) - but only Reportsand Refresh.
我可以在 sys.objects、sys.tables 中找到表格，甚至在 sys.columns 中找到它的列定义.
I can find the table in sys.objects, sys.tables and even its column definition in sys.columns.
问题是:是否可以通过任何方式访问表中的数据?我们可以中断执行以确保表保持在范围内，因此表消失应该不是问题.这不是定期或在代码中完成的事情 - 它或多或少是一次性交易.(我希望).
The question is: Is it in any way possible to access the data in the table? We can break execution so as to ensure that the table stays in scope, so the table vanishing shouldn't be an issue. This is not something that is to be done regularly or in code - it's more or less a one-shot deal. (I hope).
推荐答案
笨拙，但您可以通过管理员登录检查表格页面.
Unwieldy but you can examine the tables pages from an admin logon.
获取对象id；
select object_id from tempdb.sys.tables where name like '#mystuff%'
获取已分配页面的列表；
Get a list of allocated pages;
dbcc ind('tempdb', <object id>, -1)
对于每个 PageFID/PagePID(文件/页面 ID)
for each of the PageFID / PagePID (file/page IDs)
dbcc traceon(3604);
dbcc page(tempdb, <PageFID>, <PagePID>, 3) with tableresults
如果我从另一个会话创建 #mystuff，我可以在我自己的会话的 dbcc 页面 视图中看到:
If I create #mystuff from another session I can see in a dbcc page view from my own session:
Slot 0 Offset 0x60 Length 18 Slot 0 Column 1 Offset 0xb Length 7 Length (physical) 7 myFieldName MyValue
这篇关于是否可以读取另一个会话的临时表中的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
