sqlserver中查询数据库的索引数据

在sqlserver中，如何查询数据库中有哪些索引，本文会说到几种方式。

 

一：

SELECT  索引名称 = a.name ,
        表名 = c.name ,
        索引字段名 = d.name ,
        索引字段位置 = d.colid
FROM    sysindexes a
        JOIN sysindexkeys b ON a.id = b.id
                               AND a.indid = b.indid
        JOIN sysobjects c ON b.id = c.id
        JOIN syscolumns d ON b.id = d.id
                             AND b.colid = d.colid
WHERE   a.indid NOT IN ( 0, 255 )  
 --and   c.xtype='U'   and   c.status>0 --查所有用户表  
--AND   c.name='message' --查指定表  
ORDER BY c.name ,
        a.name ,
        d.name;

结果示例：

 

二：

exec sp_helpindex 表名

结果示例：

 

三：

WITH    info
          AS ( SELECT   TableId = O.[object_id] ,
                        TableName = O.name ,
                        IndexId = ISNULL(KC.[object_id], IDX.index_id) ,
                        IndexName = IDX.name ,
                        IndexType = ISNULL(KC.type_desc, 'Index') ,
                        Index_Column_id = IDXC.index_column_id ,
                        ColumnID = C.column_id ,
                        ColumnName = C.name ,
                        Sort = CASE INDEXKEY_PROPERTY(IDXC.[object_id],
                                                      IDXC.index_id,
                                                      IDXC.index_column_id,
                                                      'IsDescending')
                                 WHEN 1 THEN 'DESC'
                                 WHEN 0 THEN 'ASC'
                                 ELSE ''
                               END ,
                        PrimaryKey = CASE WHEN IDX.is_primary_key = 1
                                          THEN N'√'
                                          ELSE N''
                                     END ,
                        [UQIQUE] = CASE WHEN IDX.is_unique = 1 THEN N'√'
                                        ELSE N''
                                   END ,
                        Ignore_dup_key = CASE WHEN IDX.ignore_dup_key = 1
                                              THEN N'√'
                                              ELSE N''
                                         END ,
                        Disabled = CASE WHEN IDX.is_disabled = 1 THEN N'√'
                                        ELSE N''
                                   END ,
                        Fill_factor = IDX.fill_factor ,
                        Padded = CASE WHEN IDX.is_padded = 1 THEN N'√'
                                      ELSE N''
                                 END
               FROM     sys.indexes IDX
                        INNER JOIN sys.index_columns IDXC ON IDX.[object_id] = IDXC.[object_id]
                                                             AND IDX.index_id = IDXC.index_id
                        LEFT JOIN sys.key_constraints KC ON IDX.[object_id] = KC.[parent_object_id]
                                                            AND IDX.index_id = KC.unique_index_id
                        INNER JOIN sys.objects O ON O.[object_id] = IDX.[object_id]
                        INNER JOIN sys.columns C ON O.[object_id] = C.[object_id]
                                                    AND O.type = 'U'
                                                    AND O.is_ms_shipped = 0
                                                    AND IDXC.column_id = C.column_id
             )
    SELECT  *
    FROM    info a;

结果示例：

 

四：

WITH    info
          AS ( 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
             )
    SELECT  *
    FROM    info a;

结果示例：

 

 

ending~