Содержимое локальных таблиц в параллельных соединениях

Рассмотрим такую ситуацию. Предположим, что некоторое соединение создает локальную временную таблицу. Соединение начинает работу, в ходе которой временная таблица наполняется важными данными. Вдруг обнаруживается, что у соединения или у сервера возникли проблемы: соединение начинает медленно работать, или у сервера нет ресурсов для дальнейшей работы и он может вот-вот отключиться. Или происходит исчерпание места на диске из-за длительной транзакции, и соединение вот-вот завершит работу с ошибкой и с потерей данных временной таблицы. При этом все-таки хочется узнать содержимое локальной временной таблицы. Оказывается это можно сделать прочитав страницы таблицы с помощью недокументированной команды dbcc page. Однако прежде чем ей воспользоваться, необходимо сделать ряд замечаний.
Во-первых, команда недокументирована, а потому не должна участвовать в программируемых объектам в реальных базах данных. Нет гарантии что ее поддержка будет в новых версиях и нет гарантии совместимости между ее аналогами в различных версиях СУБД. Во-вторых, инструкция dbcc page получает в качестве одного из параметров номер страницы, для временной таблицы, который надо тоже как-то узнать.
Начнем с номера страницы. Как известно, каждая строка таблицы принадлежит странице, размером 8 килобайт. Нам необходимо научиться определять номера страниц для таблицы.
Рассмотрим такой пример построения и наполнения временной таблицы.
if object_id ( N'tempdb..#tmp', N'U' ) is not null

      drop table #tmp
create table #tmp
(

      iId         int               not null,

      vcName      nvarchar ( 3 )    not null

) on [PRIMARY]


insert into #tmp ( iId, vcName )

      select iId, vcName

      from

      (

            values ( 1, N'aaa' ), ( 2, N'bbb' ), ( 3, N'ccc' ), ( 4, N'ddd' ),

                ( 5, N'eee' ), ( 6, N'fff' ), ( 7, N'hhh' )
      ) data ( iId, vcName )
Теперь в другом соединении найдем идентификатор таблицы. Для этого воспользуемся каталогом sys.tables. Однако, имена временных таблиц могут совпадать для различных соединений. Поэтому при поиске идентификатора требуется закладываться не только на имя таблицы, но и на какой-нибудь дополнительный признак, например, на дату создания. Также необходимо учесть, что сервер добавляет дополнитеный текстовый суффикс в имя таблицы, поэтому вместо условия равенство на имя таблицы должно быть условие like.
declare @TabId int
select top 1 @TabId = [object_id]
from tempdb.sys.tables with ( nolock )
where name like N'#tmp%'
order by create_date desc

Теперь, используя идентификатор таблицы, можно найти список страниц таблицы. Для этого используется недокументированная инструкция dbcc ind.

declare @sql nvarchar ( max ), @PageId int, @FileId int

if object_id ( N'tempdb..#pages', N'U' ) is not null
      drop table #pages
create table #pages

(
      PageFID         int              null,
      PagePID         int              null,
      IAMFID          int              null,
      IAMPID          int              null,
      ObjectID        int              null,
      IndexID         int              null,
      PartitionNumber int              null,
      PartitionID     bigint           null,
      iam_chain_type  nvarchar ( 100 ) null,
      PageType        int              null,
      IndexLevel      int              null,
      NextPageFID     int              null,
      NextPagePID     int              null,
      PrevPageFID     int              null,
      PrevPagePI      int              null
) on [PRIMARY]
set @sql = N'dbcc ind ( 2, ' + cast ( @TabId as nvarchar ( 100 ) ) + ', 1 )'
insert into #pages
(
      PageFID,
      PagePID,
      IAMFID,
      IAMPID,
      ObjectID,
      IndexID,
      PartitionNumber,
      PartitionID,
      iam_chain_type,
      PageType,
      IndexLevel,
      NextPageFID,
      NextPagePID,
      PrevPageFID,
      PrevPagePI
)
      exec ( @sql )
declare cur cursor local static forward_only for
      select PageFID, PagePID
      from #pages
      where PageType = 1
open cur
fetch next from cur into @FileId, @PageId
while @@fetch_status = 0
begin
      select @PageId [Номер страницы], @FileId [Номер файла]
      fetch next from cur into @FileId, @PageId
end
close cur
deallocate cur
В коде выше используется идентификатор таблицы, найденный ранее. Также инструкция dbcc ind помимо номера страницы возвращает и номер файла. Он также требуется как параметр инструкции dbcc page. Мы накладываем условие на столбец PageType таблицы #pages с тем, чтобы получить только те номера страниц, на которых находятся данные таблицы. То есть не учитываем карты распределения. На каждой итерации цикла можно применить инструкцию dbcc page. Перед ее использованием требуется включить флаг трассировки 3604, чтобы поймать вывод инструкции dbcc page. Если флаг отключен, то вывод инструкции будет направлен в журнал сервера. Инструкция получает в качестве параметров идентификатор базы данных, идентификатор файла базы данных, номер страницы. Последний параметр влияет на формат вывода, в нашем случае требуется выбрать 3. Также в инструкции dbcc используется опция tableresults для табличного представления результатов. Доработаем код с циклом.
dbcc traceon ( 3604 )
if object_id ( N'tempdb..#tmpCopy', N'U' ) is not null
      drop table #tmpCopy
create table #tmpCopy
(
      iId         int               not null,
      vcName      nvarchar ( 3 )    not null
) on [PRIMARY]
if object_id ( N'tempdb..#pageCont', N'U' ) is not null
      drop table #pageCont
create table #pageCont
(
      iRowId            int identity ( 1, 1 )   not null,
      ParentObject      nvarchar ( max )        null,
      Object            nvarchar ( max )        null,
      Field             nvarchar ( max )        null,
      VALUE             nvarchar ( max )        null
) on [PRIMARY]
declare cur cursor local static forward_only for
      select PageFID, PagePID
      from #pages
      where PageType = 1
open cur
fetch next from cur into @FileId, @PageId
while @@fetch_status = 0
begin
      delete #pageCont
      set @sql = N'dbcc page ( 2, ' + cast ( @FileId as nvarchar ( 100 ) ) + ', ' + cast ( @PageId as nvarchar ( 100 ) ) + ', 3 ) with tableresults'
      insert into #pageCont ( ParentObject, Object, Field, VALUE )
            exec ( @sql )
      ;
      with DataFirCol
      as
      (
            select iRowId, cast ( VALUE as int ) iId
            from #pageCont
            where Field in ( 'iId' )
      ),
      DataSecCol
      as
      (
            select iRowId, cast ( VALUE as varchar ( 3 ) ) vcName
            from #pageCont
            where Field in ( 'vcName' )
      )
      insert into #tmpCopy ( iId, vcName )
            select DataFirCol.iId, DataSecCol.vcName
            from
                  DataFirCol
                        inner join
                  DataSecCol on DataFirCol.iRowId + 1 = DataSecCol.iRowId
      fetch next from cur into @FileId, @PageId
end
close cur
deallocate cur

select iId, vcName

from #tmpCopy
После выполнения кода таблица #tmpCopy содержит в точности те же данные, что и таблица #tmp. Точно такой же код можно применить для того, чтобы узнать содержимое табличной переменной или экземпляра табличного типа данных. Например, если в одном соединении запустить такой код:
declare @tmp table ( iId int not null, vcName nvarchar ( 3 ) not null )
insert into @tmp ( iId, vcName )
      select iId, vcName
      from
      (
            values ( 1, N'aaa' ), ( 2, N'bbb' ), ( 3, N'ccc' ), ( 4, N'ddd' ),
                 ( 5, N'eee' ), ( 6, N'fff' ), ( 7, N'hhh' )
      ) data ( iId, vcName )
waitfor delay '0:00:40'
, то в другом соединении можно найти временную таблицу в базе данных tempdb, используя каталог sys.tables и ориентируясь на дату создания объекта. Имя таблицы будет системным, но оно нам не нужно, достаточно взять object_id.