是否可以读取另一个会话临时表中的数据?
我们正在维护(偶尔调试)一个大型内部系统。该系统有 20 多个数据库,以及许多与其他系统连接、处理数据等的服务器。并非所有内容都是内部开发的 - 即我们并不总是能够访问源代码。
在一个地方,我们可以看到系统创建了一个#temp 表,然后在下一步中由于数据错误而失败。我们可以在 Management Studio 中看到 #temp 表的存在 - 它存在于 tempdb -->临时表
就像
#MyStuff_____________________________________________________________________________________________________________000000A65029
显然,这里的上下文菜单不提供完整的功能(创建表、选择前 1000 个等) - 但仅提供报告
和刷新
。
我可以在 sys.objects
、sys.tables
中找到该表,甚至可以在 sys.columns
中找到它的列定义。
问题是:是否可以通过任何方式访问表中的数据?我们可以中断执行以确保表保留在范围内,因此表消失不应该成为问题。这不是定期或在代码中完成的事情 - 这或多或少是一次性交易。 (我希望)。
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.
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
Obviously, the context menu here doesn't offer the full functionality (with Create table, select top 1000, etc.) - but only Reports
and Refresh
.
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).
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

发布评论
评论(3)
根据 Alex K 的回答,我写了一个脚本。我实际上并没有得到表中的所有行,因此可能还有改进的空间。
免责声明:与您在网上找到的任何代码一样......在运行它之前先了解它并自行承担使用风险。以 DBCC TRACEOFF(3604) 结束。
/* Run this first to get your object_id. In the case of > 1, choose one based on date and rowcount. */
DECLARE @tableName NVARCHAR(100) = '#conv'
SELECT @tableName TableName,
o.object_id,
SUM(p.rows) TableRows,
o.Create_Date CreateDatetime,
o.name TempDBFullName
FROM tempdb.sys.objects o
LEFT JOIN tempdb.sys.partitions p ON o.object_id = p.object_id
AND p.index_id <= 1
WHERE o.name LIKE @tableName+'\_\_\_%' ESCAPE '\'
GROUP BY o.name, o.object_id,o.Create_Date
--(-1074969413)
GO
/* Run this using the object_id from above to get table contents */
DECLARE
@object_id INT = -1074969413, --from above
@tableName NVARCHAR(100),
@rows BIGINT,
@created DATETIME,
@msg NVARCHAR(MAX),
--Set this to NULL for all pages
@max_pages INT = 2 --set this to restrict how many pages are read. Must be > 1.
IF @max_pages < 2
RAISERROR('You''re going to need some more pages there, Sport. Try at least 2.',16,1)
RAISERROR('
**** This code uses an undocumented feature and is for informational purposes only. Do not assume results are complete or correct. ****
',0,1)
SET NOCOUNT ON
--declare @tablename nvarchar(100), @msg nvarchar(max), @rows bigint, @created datetime
SELECT @rows = SUM(rows) FROM tempdb.sys.partitions WHERE object_id = @object_id AND index_id <= 1
SELECT @rows = ISNULL(@rows,0)
SELECT @tableName = SUBSTRING(o.name,1,CHARINDEX('____',o.name,1)-1),
@created = o.create_date
FROM tempdb.sys.objects o
WHERE o.object_id = @object_id
SELECT @msg = 'Object name '+QUOTENAME(@tableName)+' is expected to contain up to '+CAST(@rows AS NVARCHAR(20))+' rows and was created @ '+CONVERT(NVARCHAR,@created,113)+'.'
RAISERROR(@msg,0,1) WITH NOWAIT
DROP TABLE IF EXISTS #dbccind
CREATE TABLE #dbccind(PageFID NVARCHAR(100),PagePID NVARCHAR(100),IAMFID NVARCHAR(100),IAMPID NVARCHAR(100),ObjectID NVARCHAR(100),IndexID NVARCHAR(100),PartitionNumber NVARCHAR(100),PartitionID NVARCHAR(100),iam_chain_Type NVARCHAR(100),PageType NVARCHAR(100),IndexLevel NVARCHAR(100),NextPageFID NVARCHAR(100),NextPagePID NVARCHAR(100),PrevPageFID NVARCHAR(100),PrevPagePID NVARCHAR(100))
DECLARE @SQL NVARCHAR(MAX) = N'dbcc ind(''tempdb'', '+CAST(@object_id AS NVARCHAR(20))+', -1) WITH NO_INFOMSGS'
--Get a list of pages for this object
INSERT #dbccind
EXEC sp_executesql @SQL
--add an iterative counter for following loop
ALTER TABLE #dbccind ADD ID INT IDENTITY NOT NULL
--select '#dbccind' [#dbccind], * FROM #dbccind
--DECLARE @SQL nvarchar(max), @msg nvarchar(max)
DROP TABLE IF EXISTS #TempTableUnpivoted
CREATE TABLE #TempTableUnpivoted(ParentObject NVARCHAR(100), Object NVARCHAR(100), Field NVARCHAR(100), Value NVARCHAR(1000))
DBCC TRACEON(3604) WITH NO_INFOMSGS;
--Use DBCC PAGE to dump TEMPDB pages to a table as name/value pairs
IF @max_pages IS NOT NULL
BEGIN
SELECT @msg = 'Max pages set. This process will read (at most) '+CAST(@max_pages AS NVARCHAR(20))+' data page(s).'
RAISERROR(@msg,0,1) WITH NOWAIT
END
DECLARE @i INT = 1, @j INT = (SELECT MAX(id) FROM #dbccind)
WHILE @i <= @j
AND (@i <= @max_pages OR @max_pages IS NULL)
BEGIN
SELECT @SQL = 'INSERT #TempTableUnpivoted EXEC SP_EXECUTESQL N''DBCC PAGE(TEMPDB, '+CAST(PageFID AS NVARCHAR(20))+', '+CAST(PagePID AS NVARCHAR(20))+', 3) WITH TABLERESULTS, NO_INFOMSGS'''
FROM #dbccind
WHERE id = @i
SELECT @msg = 'Reading page '+CAST(@i AS NVARCHAR(20))+' of '+CAST(@j AS NVARCHAR(20))+' @ '+CONVERT(NVARCHAR,GETDATE(),113)
RAISERROR(@msg,0,1) WITH NOWAIT
--raiserror(@sql,0,1) with nowait
IF @SQL IS NULL RAISERROR('Unable to read pages.',16,1) WITH NOWAIT
EXEC SP_EXECUTESQL @SQL
SELECT @i += 1--, @SQL = NULL, @msg = NULL
END
IF @max_pages <= @i
BEGIN
SELECT @msg = 'Max pages reached. This process has read data from up to '+CAST(@max_pages AS NVARCHAR(20))+' data page(s).'
RAISERROR(@msg,0,1) WITH NOWAIT
END
--SELECT '#TempTableUnpivoted' [#TempTableUnpivoted], * FROM #TempTableUnpivoted
--get a list of fields from the page results. Assume all occur withing the first 1000 results.
SELECT @msg = 'Identify fields @ '+CONVERT(NVARCHAR,GETDATE(),113)
RAISERROR(@msg,0,1) WITH NOWAIT
DROP TABLE IF EXISTS #TableFields
CREATE TABLE #TableFields(FieldName NVARCHAR(100) NOT NULL PRIMARY KEY, ColumnId INT NOT NULL)
INSERT #TableFields(FieldName,ColumnId)
SELECT DISTINCT Field,ColumnId
FROM (SELECT TOP 1000 *, SUBSTRING(Object,CHARINDEX('Column ',Object,1)+7,CHARINDEX(' Offset ',Object,1)-CHARINDEX('Column ',Object,1)-7) ColumnId
FROM #TempTableUnpivoted
WHERE ParentObject LIKE 'Slot %'
AND Object LIKE 'Slot%Column%offset%length%') a
--Set up variables to hold a list of column names
SELECT @msg = 'Compile list of field names @ '+CONVERT(NVARCHAR,GETDATE(),113)
RAISERROR(@msg,0,1) WITH NOWAIT
--declare @sql nvarchar(max)
DECLARE @columnlist NVARCHAR(MAX) = (SELECT STRING_AGG(QUOTENAME(FieldName,''''),',') WITHIN GROUP ( ORDER BY ColumnId) FROM #TableFields)
DECLARE @columnlistquoted NVARCHAR(MAX) = (SELECT STRING_AGG(QUOTENAME(FieldName),',') WITHIN GROUP ( ORDER BY ColumnId) FROM #TableFields)
DECLARE @columnlistTyped NVARCHAR(MAX) = (SELECT STRING_AGG(QUOTENAME(FieldName),' nvarchar(1000),') WITHIN GROUP ( ORDER BY ColumnId) FROM #TableFields)+' nvarchar(1000)'
IF @columnlist IS NULL OR @columnlistquoted IS NULL OR @columnlistTyped IS NULL
BEGIN
SELECT @columnlist [@columnlist], @columnlistquoted [@columnlistquoted], @columnlistTyped [@columnlistTyped]
RAISERROR('Unable to compile columns. You might need to read more pages to get a solid list. Try incrementing @max_pages or clear @max_pages to do a full read.',16,1) WITH NOWAIT
END
--Create a list of unpivoted name/value pairs (NVP) for just the columns we need. This _may_ be able to be cut out with implicit restrictions in the pivot.
SELECT @msg = 'Reduce unpivoted data to name/value pairs @ '+CONVERT(NVARCHAR,GETDATE(),113)
RAISERROR(@msg,0,1) WITH NOWAIT
DROP TABLE IF EXISTS #TempTableNVP
CREATE TABLE #TempTableNVP(ParentObject NVARCHAR(100),Field NVARCHAR(100),Value NVARCHAR(1000))
SELECT @SQL = N'
SELECT ParentObject,Field,Value
FROM #TempTableUnpivoted
WHERE Field in ('+@columnlist+')'
INSERT #TempTableNVP(ParentObject,Field,Value)
EXEC sp_executesql @SQL
--Pivot data to final form to match temp table
SELECT @msg = 'Add fields to working table 1 @ '+CONVERT(NVARCHAR,GETDATE(),113)
RAISERROR(@msg,0,1) WITH NOWAIT
DROP TABLE IF EXISTS #TempTable
CREATE TABLE #TempTable(id INT IDENTITY NOT NULL)
SELECT @SQL = 'ALTER TABLE #TempTable ADD '+@columnlistTyped
IF @SQL IS NULL
RAISERROR('Unable to alter working table 1.',16,1) WITH NOWAIT
--raiserror(@sql,0,1) with nowait
EXEC sp_executesql @SQL
--select '#TempTable' [#TempTable], * from #TempTable
SELECT @msg = 'Pivot data to get original table @ '+CONVERT(NVARCHAR,GETDATE(),113)
RAISERROR(@msg,0,1) WITH NOWAIT
SELECT @SQL = 'INSERT #TempTable('+@columnlistquoted+')
SELECT '+@columnlistquoted+'
FROM (SELECT Field,Value,ParentObject FROM #TempTableNVP) a
PIVOT (MIN(Value) FOR Field IN ('+@columnlistquoted+')) PVT'
--raiserror(@sql,0,1) with nowait
IF @SQL IS NULL RAISERROR('Unable to populate working table 1.',16,1) WITH NOWAIT
EXEC sp_executesql @SQL
--Return results
SELECT @msg = 'Return results @ '+CONVERT(NVARCHAR,GETDATE(),113)
RAISERROR(@msg,0,1) WITH NOWAIT
--SELECT * FROM #TempTable
SELECT @SQL = 'SELECT '+@columnlistquoted+' FROM #TempTable'
EXEC sp_executesql @SQL
DBCC TRACEOFF(3604) WITH NO_INFOMSGS;
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
虽然不方便,但您可以通过管理员登录来检查表格页面。
获取对象id;
获取已分配页面的列表;
对于每个
PageFID
/PagePID
(文件/页面 ID)如果我从另一个会话创建
#mystuff
,我可以在中看到从我自己的会话中查看 dbcc 页面
:Unwieldy but you can examine the tables pages from an admin logon.
Get object id;
Get a list of allocated pages;
for each of the
PageFID
/PagePID
(file/page IDs)If I create
#mystuff
from another session I can see in adbcc page
view from my own session: