MSSQL · query the relevant information of all indexes in the database

Time:2021-6-11
Reading time |0.45 minutes word count |784 characters
primary coverage |1. Introduction & background
“MSSQL · query all index related information in database”
Author | SCscHero Writing time | 2021/5/16 AM1:56
Article type |Series Completion |Completed
motto Every great cause has a trivial beginning.

1、 Introduction & background    Completion: 100%


a) Coping with problems & scenarios

Query the information about all indexes in the DB.

b) Solution principle & method

SELECT CASE
           WHEN t.[type] = 'U' THEN
               'table '
           WHEN t.[type] = 'V' THEN
               'view'
       End as' type ',
       SCHEMA_ NAME(t.schema_ ID) + '.' + T. [name] as' (Table / view) name ',
       i. [name] as index name,
       SUBSTRING(column_ names, 1, LEN(column_ Names) - 1) as' column name ',
       CASE
           WHEN i.[type] = 1 THEN
               'clustered index'
           WHEN i.[type] = 2 THEN
               'nonclustered index'
           WHEN i.[type] = 3 THEN
               'XML index'
           WHEN i.[type] = 4 THEN
               'spatial index '
           WHEN i.[type] = 5 THEN
               'clustered column storage index'
           WHEN i.[type] = 6 THEN
               'nonclustered column storage index'
           WHEN i.[type] = 7 THEN
               'nonclustered hash index'
       End as' index type ',
       CASE
           WHEN i.is_unique = 1 THEN
               'unique'
           ELSE
               'not unique'
       End as' index unique '
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];

2、 Statement and references    Completion: 100%


Original blog, please do not reprint without permission.

If you have any help, you are welcome to like, collect and pay attention. If you have any questions, please comment! If you need to contact the blogger, you can directly send a private message to scscshero.