[ACCEPTED]-Is it possible to read data in another session's temporary table?-sql-server-2008-r2

Accepted answer
Score: 10

Unwieldy but you can examine the tables 5 pages from an admin logon.

Get object id;

select object_id from tempdb.sys.tables where name like '#mystuff%'

Get 4 a list of allocated pages;

dbcc ind('tempdb', <object id>, -1)

for each of the 3 PageFID / PagePID (file/page IDs)

dbcc traceon(3604);
dbcc page(tempdb, <PageFID>, <PagePID>, 3) with tableresults

If I create #mystuff from another 2 session I can see in a dbcc page view from my own 1 session:

Slot 0 Offset 0x60 Length 18    Slot 0 Column 1 Offset 0xb Length 7 Length (physical) 7 myFieldName MyValue
Score: 9

If you prefix a temporary table name with 5 two octothorpes, e.g. ##mystuff, it creates a global 4 temporary table that exists outside session 3 scope.

That does require you to be able to 2 alter the query text, which may or may not 1 be accessible in this specific case.

Score: 2

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

More Related questions