0

Piotr Kononow's post List all indexes in SQL Server database contains an example of SQL query to list all indexes in a SQL Server database. And we have confirmed in testing that the example works indeed.

To fully understand the query, we have a question regarding the below cross apply section, please also refer to the full query included in the details at the end.

  cross apply (
    ...
  ) D (column_names)

Our Question:

What does it mean by the D and D (column_names)?

We highly appreciate any hints and suggestions.

Details: the full sample SQL query:

--Reference:
--  https://dataedo.com/kb/query/sql-server/list-all-indexes-in-the-database

select
    i.[name] as [index_name]
  , substring(column_names, 1, len(column_names)-1) as [columns]
  , case
      when i.[type] = 1 then 'Clustered index'
      when i.[type] = 2 then 'Nonclustered unique index'
      when i.[type] = 3 then 'XML index'
      when i.[type] = 4 then 'Spatial index'
      when i.[type] = 5 then 'Clustered columnstore index'
      when i.[type] = 6 then 'Nonclustered columnstore index'
      when i.[type] = 7 then 'Nonclustered hash index'
      else 'ERROR'
    end as [index_type]
  , case
      when i.is_unique = 1 then 'Unique'
      else 'Not unique'
    end as [unique]
  , '['+schema_name(t.schema_id)+'].['+t.[name]+']' as [table_view]
  , case
      when t.[type] = 'U' then 'Table'
      when t.[type] = 'V' then 'View'
    end as [object_type]
from
  sys.objects t
  inner join sys.indexes i
    on t.object_id = i.object_id
  cross apply (
    select
      col.[name]+', '
    from
      sys.index_columns ic
      inner join sys.columns col
        on ic.object_id = col.object_id and ic.column_id = col.column_id
    where
      ic.object_id = t.object_id
      and ic.index_id = i.index_id
    order by key_ordinal
    for xml path ('')
  ) D (column_names)
where
  t.is_ms_shipped <> 1
  and index_id > 0
order by i.[name]

;

3
  • It's just a virtual name for the result of the apply operator. You can use whatever name you want. Oct 27, 2023 at 3:03
  • 3
    When in doubt consult the docs [ [ AS ] table_alias ] [ (column_alias [ , ...n ] ) ] Oct 27, 2023 at 9:21
  • 1
    Side note: this query is using for xml for aggregation, but it's not doing XML unescaping. It should be select ', ' + col.name and for xml path (''), type) and , stuff(column_names, 1, 2, '') as [columns]. Or on newer versions of SQL Server, just use STRING_AGG Oct 27, 2023 at 9:23

1 Answer 1

5

What does it mean by the D and D (column_names)?

D represent as a table alias while the column_names is for column alias for col.[name]+', '.

The column_names was used on SELECT list: substring(column_names, 1, len(column_names)-1) as [columns]

To learn more about apply operator I'd recommend to read these links: link 1 and link 2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.