Show Me The Index 😁

 SELECT '[' + s.NAME + '].[' + o.NAME + ']' AS 'table_name'
    , i.NAME AS 'index_name'
    , LOWER(i.type_desc)
        + CASE WHEN i.is_unique = 1 THEN ', unique' ELSE '' END
        + CASE WHEN i.is_primary_key = 1 THEN ', primary key' ELSE '' END
	  AS 'index_description'
  	, i.is_unique AS 'Is_Unique'
    , STUFF((
        SELECT ', [' + sc.NAME + ']' AS "text()"
        FROM syscolumns AS sc
        INNER JOIN sys.index_columns AS ic
            ON ic.object_id = sc.id
            AND ic.column_id = sc.colid
        WHERE sc.id = so.object_id
            AND ic.index_id = i1.indid
            AND ic.is_included_column = 0
        ORDER BY key_ordinal
        FOR XML PATH('')
        ), 1, 2, '') AS 'indexed_columns'
    , STUFF((
        SELECT ', [' + sc.NAME + ']' AS "text()"
        FROM syscolumns AS sc
        INNER JOIN sys.index_columns AS ic ON ic.object_id = sc.id
                AND ic.column_id = sc.colid
        WHERE sc.id = so.object_id
                AND ic.index_id = i1.indid
                AND ic.is_included_column = 1
        FOR XML PATH('')
        ), 1, 2, '') AS 'included_columns'
FROM sysindexes AS i1
INNER JOIN sys.indexes AS i
	ON i.object_id = i1.id
	AND i.index_id = i1.indid
INNER JOIN sysobjects AS o
	ON o.id = i1.id
INNER JOIN sys.objects AS so
	ON so.object_id = o.id
	AND is_ms_shipped = 0
INNER JOIN sys.schemas AS s
	ON s.schema_id = so.schema_id
WHERE so.type = 'U'
    AND i1.indid < 255
    AND i1.STATUS & 64 = 0 --index with duplicates
    AND i1.STATUS & 8388608 = 0 --auto created index
    AND i1.STATUS & 16777216 = 0 --stats no recompute
    AND i.type_desc <> 'heap'
    AND so.NAME <> 'sysdiagrams'
    -- 테이블이름 AND o.NAME = 'TB_Maind5'
ORDER BY table_name, index_name;

 

show me the index  info

인덱스 정보

알려줘