Piotr Kononow 的帖子列出 SQL Server 数据库中的所有索引包含一个 SQL 查询示例,用于列出 SQL Server 数据库中的所有索引。我们在测试中证实该示例确实有效。
为了充分理解该查询,我们对以下cross apply
部分有一个疑问,另请参阅末尾详细信息中包含的完整查询。
cross apply (
...
) D (column_names)
我们的问题:
D
和是什么意思D (column_names)
?
我们非常感谢任何提示和建议。
详细信息:完整的 SQL 查询示例:
--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]
;
D
表示为表别名,而column_names
is 表示列别名col.[name]+', '
。用于
column_names
SELECT 列表:substring(column_names, 1, len(column_names)-1) as [columns]
要了解有关 apply 运算符的更多信息,我建议阅读这些链接:链接 1和链接 2。