Split по списку разделителей на T-SQL

Обычно T-SQL не часто используется для синтаксического анализа текста (например разбивки предложений на слова). Для этого больше подходят языки с поддержкой регулярных выражений. Но иногда все же возникает необходимость в разборе текста именно на стороне SQL-сервера. Скажем, при импорте текстовых файлов в БД с некоторым процессингом, когда недостачно функциональности bcp, BULK INSERT и SSIS-пакетов. Сегодня я поделюсь своей реализацией функции разбивки текста на фрагменты (split) по заданному списку разделителей. Работает в SQL Server 2008 и 2012.

Поскольку в T-SQL нет массивов или списков, вначале создадим табличный тип для хранения разделителей:

CREATE TYPE [dbo].[StringArray] AS TABLE(
 [String] nvarchar(max) NULL
)

Теперь функция:

CREATE FUNCTION [dbo].[SplitOn](
 @String nvarchar(max),
 @Separators StringArray readonly
)
RETURNS @Fragments TABLE (
 FragmentNumber int,
 Fragment nvarchar(max)
)
AS
BEGIN
 IF @String IS NULL RETURN
 
 IF NOT EXISTS (SELECT 1 FROM @Separators)
 BEGIN
  INSERT @Fragments SELECT 1, @String
  RETURN
 END
 
 DECLARE @SepLengths TABLE (
  Separator nvarchar(max), [Length] int
 )
 
 INSERT @SepLengths 
 SELECT [String], DATALENGTH([String]) / 2 FROM @Separators
 
 DECLARE @CurrentPosition int = 1, @OldPosition int = 1,
 @FragmentLength int = 0, @FragmentNumber int = 1,
 @Length int = DATALENGTH(@String) / 2, @Fragment nvarchar(max)
 
 WHILE @CurrentPosition IS NOT NULL
 BEGIN
  SELECT @CurrentPosition = NULL
  
  SELECT TOP 1 @CurrentPosition = [Position],
  @FragmentLength = [Length]
  FROM 
   (
    SELECT 
    [Position] = 
    CHARINDEX(Separator, @String, @OldPosition),
    [Length] FROM @SepLengths
   ) p
  WHERE [Position] > 0 ORDER BY [Position], [Length] DESC
  
  IF @CurrentPosition IS NULL
  BEGIN
   SELECT @Fragment = 
   SUBSTRING(@String, @OldPosition, 
   @Length - @OldPosition + 1)
   
   INSERT @Fragments
   SELECT @FragmentNumber, @Fragment
  END
  ELSE
  BEGIN
   SELECT @Fragment = 
   SUBSTRING(@String, @OldPosition,
   @CurrentPosition - @OldPosition)
   
   INSERT @Fragments SELECT @FragmentNumber, @Fragment
   
   SELECT @FragmentNumber = @FragmentNumber + 1,
   @OldPosition = @CurrentPosition + @FragmentLength
  END
 END
 
 RETURN
END

И пример использования:

DECLARE @String nvarchar(max) = N'Воры! Пожар! Убивают! Ничего подобного не случалось с тех самых пор, как он обосновался под Горой. Ярость дракона не поддается описанию. Разве что можно ее сравнить с бешенством какого-нибудь толстосума, привыкшего к своим богатствам и нежданно утратившего что-то, чем он почти не пользовался или чего вообще не трогал в течение многих лет. Смог выпустил из пасти струю пламени; пещера наполнилась дымом, Гора сотряслась от макушки до основания. Дракон попытался просунуть голову в отверстие. Убедившись, что оно слишком маленькое, Смог взревел — под землей словно прогремел гром — и ринулся по подземным коридорам к Парадным Вратам.'

DECLARE @Separators StringArray
INSERT @Separators VALUES ('.'), (','), ('!'), (';'), ('—')

SELECT * FROM dbo.SplitOn(@String, @Separators)

Результат выполнения:

Небольшое пояснение к алгоритму разбиения. По мере продвижения по строке вправо функция находит тот разделитель из списка, который встречается раньше других. Если есть вхождение двух разделителей с одной и той же позиции (такое возможно, когда один из разделителей входит с начала в другой), то выбирается самый длинный разделитель. Это демонстрируется следующим примером:

DECLARE @String nvarchar(max) = N'1,2,,3'

DECLARE @Separators1 StringArray, @Separators2 StringArray
INSERT @Separators1 VALUES (',')
INSERT @Separators2 VALUES (','), (',,')

SELECT * FROM dbo.SplitOn(@String, @Separators1)
SELECT * FROM dbo.SplitOn(@String, @Separators2)

Подробная статистика использования индексов в MS SQL Server 2008/2012

Одна из задач, которую разработчикам и DBA приходится решать для улучшения производительности базы данных  это удаление лишних индексов, которые не используются при выборках, а только занимают место в базе и замедляют изменение данных. В таких случаях очень полезной может оказаться замечательная статья Грега Робиду "How to get index usage information in SQL Server".
Следующий запрос, взятый из этой статьи позволяет получить статистику всех операций, произведенных над индексом с момента старта SQL сервера:

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 

Однако часто самого факта, что индекс используется может оказаться недостаточно и хотелось бы знать, какие именно запросы этот индекс используют. Когда мы имеем дело с одним запросом, то легко увидеть, какие индексы он использует, проанализировав план его выполнения. Будем использовать тот же подход и в случае множества запросов. Вначале получим статистику о выполненных запросах из кэша планов сервера:

SELECT qs.query_hash, qs.query_plan_hash, st.[text], qp.query_plan, qs.creation_time, qs.last_execution_time, qs.execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

Возвращаемые столбцы:

query_hash – хэш SQL-предложения (часть пакета)
query_plan_hash – хэш плана всего запроса
text – текст всего пакета или процедуры
query_plan – план запроса в виде xml (можно посмотреть его в SQL Server Management Studio, если сохранить его этот xml с расширением .sqlplan)
creation_time – когда план был скомпилирован
last_execution_time – когда запрос был выполнен по этому плану в последний раз
execution_count – сколько раз запрос был выполнен по этому плану

Затем из xml-плана каждого запроса с помощью методов типа данных xml получим информацию о том, какие индексы были задействованы и какие операции над ними выполнялись. Это достаточно несложно сделать, т.к xml плана запроса подчиняется схеме:
http://schemas.microsoft.com/sqlserver/2004/07/showplan

Например, для запроса (база данных master):

SELECT TOP 10 * FROM sys.objects ORDER BY [name]

На моем SQL Server 2008 генерируется такой план:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.2550.0">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT TOP 10 * FROM sys.objects ORDER BY [name]" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0240863" StatementEstRows="10" StatementOptmLevel="FULL" QueryHash="0xFBA5FED7D0BBA861" QueryPlanHash="0x2E11BC9EAFF10957" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
          <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
          <QueryPlan CachedPlanSize="56" CompileTime="7" CompileCPU="7" CompileMemory="864">
            <RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="10" EstimateIO="0" EstimateCPU="1e-006" AvgRowSize="134" EstimatedTotalSubtreeCost="0.0240863" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
                <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" />
                <ColumnReference Column="Expr1006" />
                <ColumnReference Column="Expr1009" />
                <ColumnReference Column="Expr1010" />
              </OutputList>
              <Top RowCount="0" IsPercent="0" WithTies="0">
                <TopExpression>
                  <ScalarOperator ScalarString="(10)">
                    <Const ConstValue="(10)" />
                  </ScalarOperator>
                </TopExpression>
                <RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00039292" AvgRowSize="134" EstimatedTotalSubtreeCost="0.0240853" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
                    <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" />
                    <ColumnReference Column="Expr1006" />
                    <ColumnReference Column="Expr1009" />
                    <ColumnReference Column="Expr1010" />
                  </OutputList>
                  <NestedLoops Optimized="0">
                    <OuterReferences>
                      <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                    </OuterReferences>
                    <RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00039292" AvgRowSize="72" EstimatedTotalSubtreeCost="0.0191962" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
                        <ColumnReference Column="Expr1006" />
                        <ColumnReference Column="Expr1009" />
                        <ColumnReference Column="Expr1010" />
                      </OutputList>
                      <NestedLoops Optimized="0">
                        <OuterReferences>
                          <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                        </OuterReferences>
                        <RelOp NodeId="3" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00014852" AvgRowSize="68" EstimatedTotalSubtreeCost="0.0143071" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                            <ColumnReference Column="Expr1006" />
                            <ColumnReference Column="Expr1009" />
                            <ColumnReference Column="Expr1010" />
                          </OutputList>
                          <Filter StartupExpression="0">
                            <RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="10" EstimateIO="0" EstimateCPU="9.4e-006" AvgRowSize="70" EstimatedTotalSubtreeCost="0.0142913" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                              <OutputList>
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                                <ColumnReference Column="Expr1006" />
                                <ColumnReference Column="Expr1009" />
                                <ColumnReference Column="Expr1010" />
                              </OutputList>
                              <ComputeScalar>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1006" />
                                    <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&amp;(1),0)">
                                      <Convert DataType="bit" Style="0" Implicit="0">
                                        <ScalarOperator>
                                          <Arithmetic Operation="BIT_AND">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="(1)" />
                                            </ScalarOperator>
                                          </Arithmetic>
                                        </ScalarOperator>
                                      </Convert>
                                    </ScalarOperator>
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1009" />
                                    <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&amp;(16),0)">
                                      <Convert DataType="bit" Style="0" Implicit="0">
                                        <ScalarOperator>
                                          <Arithmetic Operation="BIT_AND">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="(16)" />
                                            </ScalarOperator>
                                          </Arithmetic>
                                        </ScalarOperator>
                                      </Convert>
                                    </ScalarOperator>
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1010" />
                                    <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&amp;(64),0)">
                                      <Convert DataType="bit" Style="0" Implicit="0">
                                        <ScalarOperator>
                                          <Arithmetic Operation="BIT_AND">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="(64)" />
                                            </ScalarOperator>
                                          </Arithmetic>
                                        </ScalarOperator>
                                      </Convert>
                                    </ScalarOperator>
                                  </DefinedValue>
                                </DefinedValues>
                                <RelOp NodeId="5" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00039292" AvgRowSize="73" EstimatedTotalSubtreeCost="0.0142903" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                                  <OutputList>
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                                  </OutputList>
                                  <NestedLoops Optimized="0">
                                    <OuterReferences>
                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                    </OuterReferences>
                                    <RelOp NodeId="6" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="10" EstimateIO="0.003125" EstimateCPU="0.0002604" AvgRowSize="45" EstimatedTotalSubtreeCost="0.003293" TableCardinality="94" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                                      <OutputList>
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" />
                                      </OutputList>
                                      <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[master]" Schema="[sys]" Table="[sysschobjs]" Index="[nc2]" Alias="[o]" IndexKind="NonClustered" />
                                      </IndexScan>
                                    </RelOp>
                                    <RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="34" EstimatedTotalSubtreeCost="0.0109555" TableCardinality="94" Parallel="0" EstimateRebinds="9.89362" EstimateRewinds="0">
                                      <OutputList>
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                                      </OutputList>
                                      <IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[master]" Schema="[sys]" Table="[sysschobjs]" Index="[clst]" Alias="[o]" TableReferenceId="-1" IndexKind="Clustered" />
                                        <SeekPredicates>
                                          <SeekPredicateNew>
                                            <SeekKeys>
                                              <Prefix ScanType="EQ">
                                                <RangeColumns>
                                                  <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                                </RangeColumns>
                                                <RangeExpressions>
                                                  <ScalarOperator ScalarString="[master].[sys].[sysschobjs].[id] as [o].[id]">
                                                    <Identifier>
                                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </RangeExpressions>
                                              </Prefix>
                                            </SeekKeys>
                                          </SeekPredicateNew>
                                        </SeekPredicates>
                                      </IndexScan>
                                    </RelOp>
                                  </NestedLoops>
                                </RelOp>
                              </ComputeScalar>
                            </RelOp>
                            <Predicate>
                              <ScalarOperator ScalarString="has_access('CO',[master].[sys].[sysschobjs].[id] as [o].[id])=(1) AND [master].[sys].[sysschobjs].[nsclass] as [o].[nsclass]=(0) AND [master].[sys].[sysschobjs].[pclass] as [o].[pclass]=(1)">
                                <Logical Operation="AND">
                                  <ScalarOperator>
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Intrinsic FunctionName="has_access">
                                          <ScalarOperator>
                                            <Const ConstValue="'CO'" />
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="" />
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="" />
                                          </ScalarOperator>
                                        </Intrinsic>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="(1)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="(0)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="(1)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Logical>
                              </ScalarOperator>
                            </Predicate>
                          </Filter>
                        </RelOp>
                        <RelOp NodeId="30" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00484728" TableCardinality="167" Parallel="0" EstimateRebinds="9.89362" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
                          </OutputList>
                          <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Index="[clst]" Alias="[r]" IndexKind="Clustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depid" />
                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="class" />
                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depsubid" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="[master].[sys].[sysschobjs].[id] as [o].[id]">
                                        <Identifier>
                                          <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator ScalarString="(97)">
                                        <Const ConstValue="(97)" />
                                      </ScalarOperator>
                                      <ScalarOperator ScalarString="(0)">
                                        <Const ConstValue="(0)" />
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                    <RelOp NodeId="31" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="71" EstimatedTotalSubtreeCost="0.0048641" TableCardinality="127" Parallel="0" EstimateRebinds="6.70213" EstimateRewinds="3.29787">
                      <OutputList>
                        <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" />
                      </OutputList>
                      <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Index="[cl]" Alias="[n]" IndexKind="Clustered" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="class" />
                                  <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="value" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="'OBTY'">
                                    <Const ConstValue="'OBTY'" />
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[master].[sys].[sysschobjs].[type] as [o].[type]">
                                    <Identifier>
                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </Top>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Для упрощения запросов к такому xml первым делом удалим namespace (в данном случае строку xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan). Это можно сделать например такой функцией:

USE [master]

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RemoveNamespaceString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[RemoveNamespaceString]

GO

CREATE FUNCTION [dbo].[RemoveNamespaceString](@xml xml, @namespaceString nvarchar(max))
RETURNS xml
AS
BEGIN
 DECLARE @xc nvarchar(max) = CAST(@xml as nvarchar(max))
 DECLARE @ci int = CHARINDEX(@namespaceString, @xc)
 IF @ci = 0 RETURN @xml
 RETURN CAST(STUFF(@xc, @ci, LEN(@namespaceString), '') as xml)
END

На входе – xml с namespace-ом и сам namespace. На выходе – xml без namespace-а. Функция не отличается изяществом и просто удаляет первое вхождение строки @namespaceString.
Теперь функция, возвращающая табличное представление информации об индексах:

USE [master]

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IndexesUsedByPlan]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[IndexesUsedByPlan]

GO


CREATE FUNCTION [dbo].[IndexesUsedByPlan](@xml xml)
RETURNS TABLE
AS
RETURN
(
 SELECT * FROM
 (
  SELECT
  statement_xml = s.query('.'),
  operation_xml = op.query('.'),
  object_xml = obj.query('.'),
  statement_id = s.value('.[1]/@StatementId', 'nvarchar(max)'),
  statement_text = s.value('.[1]/@StatementText', 'nvarchar(max)'),
  query_hash = convert(binary(8), s.value('.[1]/@QueryHash', 'varchar(max)'), 1),
  query_plan_hash = convert(binary(8), s.value('.[1]/@QueryPlanHash', 'nvarchar(max)'), 1),
  operation = op.value('.[1]/@PhysicalOp', 'nvarchar(max)'),
  index_database = obj.value('.[1]/@Database', 'nvarchar(max)'),
  index_schema = obj.value('.[1]/@Schema', 'nvarchar(max)'),
  index_table = obj.value('.[1]/@Table', 'nvarchar(max)'),
  [index] = obj.value('.[1]/@Index', 'nvarchar(max)'),
  index_kind = obj.value('.[1]/@IndexKind', 'nvarchar(max)')
  FROM @xml.nodes('//StmtSimple') T(s)
  CROSS APPLY s.nodes('.//RelOp') S(op)
  CROSS APPLY op.nodes('.//Object') Ro(obj)
 ) p 
 WHERE operation LIKE '%Index%' and [index] IS NOT NULL 

)

@xml – xml плана (без namespace-а)

Возвращаемые столбцы:

statement_xml – часть плана, содержащая данные о том SQL-предложении (часть batch-а или процедуры), которое задействовало индекс
operation_xml – часть плана, содержащая данные об операции с индексом (такой, как Index Seek или Index Scan)
object_xml – часть плана, содержащая данные об объекте, (в данном случае - индексе), над которым производилась операция
statement_id – порядковый номер SQL-предложения в batch-е или процедуре
statement_text – текст SQL-предложения
query_hash – хэш SQL-предложения
query_plan_hash – хэш плана запроса
operation – операция, которая производится с индексом (например "Index Scan")
index_database – база данных, в которой находится индекс
index_schema – схема таблицы, которой принадлежит индекс
index_table – имя таблицы, которой принадлежит индекс. Дополнительное
index – имя индекса
index_kind – тип индекса (Clustered/NonClustered)

И наконец запрос, выбирающий все нужные данные:

SELECT 
pind.statement_text, query_text = st.[text],
pind.statement_id, qs.statement_start_offset, qs.statement_end_offset,
qs.creation_time, qs.last_execution_time, qs.execution_count,
pind.index_database, pind.index_schema, pind.index_table, [index], pind.index_kind,
pind.query_hash, pind.query_plan_hash,
qp.query_plan, pind.statement_xml, pind.operation_xml, pind.object_xml
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY (
 SELECT p.* FROM
 [master].dbo.IndexesUsedByPlan( 
  [master].dbo.RemoveNamespaceString(qp.query_plan,
  'xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"')) p
 JOIN
 (
  SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE
  table_type='BASE TABLE'
 ) ut
 ON p.index_schema COLLATE DATABASE_DEFAULT = '[' + ut.TABLE_SCHEMA + ']'
 AND p.index_table COLLATE DATABASE_DEFAULT = '[' + ut.TABLE_NAME + ']'
 WHERE p.index_database = '[' + DB_NAME() + ']'
) pind
WHERE
qp.query_plan IS NOT NULL
AND qs.query_hash = pind.query_hash
AND qs.query_plan_hash = pind.query_plan_hash
ORDER BY pind.[index_table], pind.[index]

JOIN с выборкой из представления information_schema.tables нужен, чтобы ограничить возвращаемые индексы только текущей базой данных и только пользовательскими таблицами.