Est-il possible de lire des données dans la table temporaire d'une autre session?

Basé sur la réponse d'Alex K, j'ai écrit un script. Je n'ai pas fini avec toutes les lignes de la table, alors il y a probablement une place d'amélioration. Disclaimer: Comme avec n'importe quel code que vous trouvez en ligne ... Comprenez-le avant de l'exécuter et d'utiliser à vos risques et périls. Se termine par 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;