List all indexes of a particular table - SQL Server

As in recent work with the client, I got the question of finding the indexes to be applied on a particular table in the SQL server.

If you want to have listed all the indexes from a particular table from the SQL server, then now you just have to write your table name in the variable and execute the below query. And see the result.

DECLARE @TABLENAME VARCHAR(50) = '<table_name>'

SELECT '[' + s.name + '].[' + sObj.name + ']' AS 'TableName'
    ,+ ind.name AS 'IndexName'
    ,ind.type_desc AS 'IndexType'
    ,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 = Obj.object_id
                AND ic.index_id = sind.indid
                AND ic.is_included_column = 0
            ORDER BY key_ordinal
            FOR XML PATH('')
            ), 1, 2, '') AS 'IndexedColumns'
FROM sysindexes AS sind
INNER JOIN sys.indexes AS ind ON ind.object_id = sind.id
    AND ind.index_id = sind.indid
INNER JOIN sysobjects AS sObj ON sObj.id = sind.id
INNER JOIN sys.objects AS Obj ON Obj.object_id = sObj.id
    AND is_ms_shipped = 0
INNER JOIN sys.schemas AS s ON s.schema_id = Obj.schema_id
WHERE ind.object_id = OBJECT_ID(@TABLENAME)
    AND ind.is_primary_key = 0
    AND ind.is_unique = 0
    AND ind.is_unique_constraint = 0
ORDER BY TableName
    ,IndexName;

Output: