SQL Server 查询数据库中的所有索引

我们时常需要关注一下自己的数据库是否需要重建索引，因为索引产生大量的碎片，这个时候需要删除索引进行重建，重建索引需要快速知道当前数据下的所有索引信息，你可以使用下面的 T-Sql 查询相关信息

批量重建索引可以点击访问 SQL Server 批量重建索引

SELECT CASE
           WHEN t.[type] = 'U' THEN
               '表'
           WHEN t.[type] = 'V' THEN
               '视图'
       END AS '类型',
       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS '(表/视图)名称',
       i.[name] AS 索引名称,
       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS '列名',
       CASE
           WHEN i.[type] = 1 THEN
               '聚集索引'
           WHEN i.[type] = 2 THEN
               '非聚集索引'
           WHEN i.[type] = 3 THEN
               'XML索引'
           WHEN i.[type] = 4 THEN
               '空间索引'
           WHEN i.[type] = 5 THEN
               '聚簇列存储索引'
           WHEN i.[type] = 6 THEN
               '非聚集列存储索引'
           WHEN i.[type] = 7 THEN
               '非聚集哈希索引'
       END AS '索引类型',
       CASE
           WHEN i.is_unique = 1 THEN
               '唯一'
           ELSE
               '不唯一'
       END AS '索引是否唯一'
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 col.column_id
    FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
      AND index_id > 0
ORDER BY i.[name];

查看我本人更多原创文章，请点击这里。