Based on Alex K's answer I wrote up a script. I didn't actually end up with all the rows from the table, so there's likely room for improvement.
Disclaimer: As with any code you find online... understand it before you run it and use at your own risk. Ends with 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;
