-------------------------------
-- Nicolas Souquet - 04/06/2012
-------------------------------
 
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
 
DECLARE  @schema_name sysname = 'dbo'
  , @table_or_view_name sysname --= 'maTable' -- si NULL, étudie tous les index de toutes les table du schéma
  , @index_name sysname = NULL -- si NULL, étudie tous les index de la table
  , @separator varchar(2) = ', '
----------------------------------------------------------------------------------------------------------------------
SELECT    DISTINCT S.name + '.' + O.name AS schema_table_name
    , I.name AS index_name
    , PS.row_count
    , I.type_desc
    , LEFT(KC.key_column_list, LEN(KC.key_column_list) - 2) AS key_column_list
    , LEFT(KCI.included_key_column_list, LEN(KCI.included_key_column_list) - 1) AS included_key_column_list
    , REPLACE(REPLACE(REPLACE(REPLACE(I.filter_definition, '[', ''), ']', ''), '(', ''), ')', '') AS filter_definition
    , PS.used_page_count AS pages_count
    , STATS_DATE(O.object_id, I.index_id) AS last_stat_update
    , IUS.user_seeks
    , IUS.user_scans
    , IUS.user_lookups
    , (IUS.user_seeks + IUS.user_scans + IUS.user_lookups) AS total_user_searches
    , IUS.last_user_seek
    , IUS.last_user_scan  
    , IUS.last_user_lookup
    , LS.max_last_search_date
    , PS.used_page_count / 8 AS index_size_kb
FROM    sys.schemas AS S
INNER JOIN  sys.objects AS O
      ON S.schema_id = O.schema_id
INNER JOIN  sys.indexes AS I  
      ON O.object_id = I.object_id
INNER JOIN  sys.index_columns AS IC
      ON IC.object_id = I.object_id
      AND IC.index_id = I.index_id
INNER JOIN  sys.columns AS C
      ON IC.object_id = C.object_id
      AND IC.column_id = C.column_id
LEFT JOIN  sys.dm_db_index_usage_stats AS IUS
      ON IUS.object_id = I.object_id
      AND IUS.index_id = I.index_id
      AND IUS.database_id = DB_ID()
INNER JOIN  sys.dm_db_partition_stats AS PS
      ON PS.object_id = I.object_id
      AND PS.index_id = I.index_id
CROSS APPLY  (
      SELECT    CS.name + @separator
      FROM    sys.columns AS CS
      INNER JOIN  sys.index_columns AS ICS
            ON CS.object_id = ICS.object_id
            AND CS.column_id = ICS.column_id
      WHERE    IC.object_id = ICS.object_id
      AND    IC.index_id = ICS.index_id
      AND    ICS.is_included_column = 0
      ORDER BY  ICS.index_column_id
      FOR XML PATH ('')
    ) AS KC (key_column_list)
OUTER APPLY  (
      SELECT    CSI.name + @separator
      FROM    sys.columns AS CSI
      INNER JOIN  sys.index_columns AS ICSI
            ON CSI.object_id = ICSI.object_id
            AND CSI.column_id = ICSI.column_id
      WHERE    IC.object_id = ICSI.object_id
      AND    IC.index_id = ICSI.index_id
      AND    ICSI.is_included_column = 1
      ORDER BY  ICSI.index_column_id
      FOR XML PATH ('')
    ) AS KCI (included_key_column_list)
OUTER APPLY  (
      SELECT  MAX(M.last_search_date)
      FROM  (
            SELECT  LU_USK.last_user_seek
            FROM  sys.dm_db_index_usage_stats AS LU_USK
            WHERE  IUS.database_id = LU_USK.database_id
            AND  IUS.object_id = LU_USK.object_id
            AND  IUS.index_id = LU_USK.index_id
          UNION ALL
            SELECT  LU_USC.last_user_scan
            FROM  sys.dm_db_index_usage_stats AS LU_USC
            WHERE  IUS.database_id = LU_USC.database_id
            AND  IUS.object_id = LU_USC.object_id
            AND  IUS.index_id = LU_USC.index_id
          UNION ALL
            SELECT  LU_LK.last_user_lookup
            FROM  sys.dm_db_index_usage_stats AS LU_LK
            WHERE  IUS.database_id = LU_LK.database_id
            AND  IUS.object_id = LU_LK.object_id
            AND  IUS.index_id = LU_LK.index_id
        ) AS M (last_search_date)
    ) AS LS (max_last_search_date)
WHERE    (@schema_name IS NULL OR S.name = @schema_name)
AND    (@table_or_view_name IS NULL OR O.name = @table_or_view_name)
AND    (@index_name IS NULL OR I.name = @index_name)
ORDER BY  I.type_desc