MSSQL: list tables, indexes, indexed columns, etc

gistfile1.sql

--Source: http://simplesqlserver.com/2013/11/20/indexing-fundamentals/

DECLARE @TableName VarChar(100)
SELECT @TableName = '%'

SELECT TableName = i.SchemaName + '.' + i.TableName
    , IndexName = ISNULL(i.IndexName, '[' + Lower(i.IndexType) + ']')
    --, SeekUpdateRatio = CASE WHEN i.User_Updates > 0 THEN CAST(i.User_Seeks / CAST(i.User_Updates as DEC(20,1)) as DEC(20,2)) ELSE 0 END
    , i.User_Updates
    , i.User_Seeks
    , i.User_Scans
    , i.User_Lookups
    , KeyColumnList = substring((SELECT (', ' + c.name)
                            FROM sys.index_columns ic
                                INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                            WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id
                                AND ic.is_included_column = 0
                            ORDER BY ic.key_ordinal
                            FOR XML PATH ('')
                            ), 3, 2000)
    , IncludedColumnList = CASE WHEN i.IndexType IN ('Clustered', 'Heap') THEN '*' ELSE
                            substring((SELECT (', ' + c.name)
                            FROM sys.index_columns ic
                                INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                            WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id
                                AND ic.is_included_column = 1
                            ORDER BY c.Name
                            FOR XML PATH ('')
                            ), 3, 2000)
                        END
    , i.filter_definition
    , i.is_unique
    --, i.StartTime
    , i.Used_MB
    , i.Reserved_MB
    , Free_MB = i.Reserved_MB - i.Used_MB
    , i.Row_Count
    --, DropStatement = 'DROP INDEX [' + i.IndexName + '] ON [' + i.SchemaName + '].[' + i.TableName + ']'
FROM
    (
    SELECT SchemaName = s.name
        , TableName = t.name
        , IndexName = i.name
        , IndexType = i.type_desc
        , i.object_id 
        , i.index_id
        , i.filter_definition
        , i.is_unique
        , User_Updates = SUM(ius.User_Updates)
        , User_Seeks = SUM(ius.user_seeks)
        , User_Scans = SUM(ius.user_scans)
        , User_Lookups = SUM(ius.user_lookups)
        --, StartTime = MIN(ius.StartTime)   
        , i.Used_MB
        , i.Reserved_MB
        , i.row_count
    FROM (SELECT i.name
                , i.type_desc
                , i.object_id
                , i.index_id
                , i.is_unique
                , i.filter_definition
                , Used_MB = SUM(PS.used_page_count) / 128
                , Reserved_MB = SUM(PS.reserved_page_count) / 128
                , row_count = SUM(PS.row_count)
            FROM sys.indexes i
                LEFT JOIN sys.dm_db_partition_stats PS ON i.object_id = PS.object_id AND i.index_id = PS.index_id
            GROUP BY i.name, i.type_desc, i.object_id, i.index_id, i.is_unique, i.filter_definition) i
        INNER JOIN sys.all_objects t ON i.object_id = t.object_id
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        LEFT JOIN /*DBA..Index_Usage_Stats*/ sys.dm_db_index_usage_stats ius ON ius.object_id = i.object_id
                                            AND ius.index_id = i.index_id
                                            AND ius.database_id = DB_ID()
                                            --AND DatePart(Hour, ius.EndTime) BETWEEN 10 AND 17
                                            --AND DatePart(DW, ius.EndTime) BETWEEN 2 AND 6
       
    WHERE t.name LIKE @TableName     
    GROUP BY s.name, t.name, i.name, i.object_id, i.index_id, i.is_unique, i.type_desc, i.filter_definition, i.Used_MB, i.Reserved_MB, i.row_count
    ) i
ORDER BY 1, KeyColumnList