-------------------------------
-- Nicolas SOUQUET - 04/05/2010
-------------------------------
CREATE PROCEDURE ps_get_index_stats
  @_table_name sysname = NULL
AS
BEGIN
  SET NOCOUNT ON
 
  -- Si le nom de la table n'exsite pas,
  -- alors cela revient à passer NULL en object_id à la fonction sys.dm_db_index_physical_stats
  -- ce qui a pour effet de collecter toutes les statististiques de tous les index de la BD
  IF
  (
    @_table_name IS NOT NULL
    AND NOT EXISTS
    (
      SELECT  *
      FROM  sys.tables
      WHERE  name = @_table_name
    )
  )
  BEGIN
    RAISERROR('The table %s does not exist', 16, 1, @_table_name)
    RETURN
  END
 
  SELECT    D.name AS database_name
      , O.name AS table_name
      , I.name AS index_name
      , CASE IPS.index_type_desc
        WHEN 'CLUSTERED INDEX' THEN 'CL'
        WHEN 'NONCLUSTERED INDEX' THEN 'N-CL'
        ELSE IPS.index_type_desc
      END AS index_type
      , IUS.user_updates
      , IUS.user_seeks
      , IUS.user_scans
      , IUS.last_user_seek
      , IUS.last_user_scan
      , IPS.index_depth
      , IPS.page_count
      , CAST(IPS.avg_fragmentation_in_percent AS decimal(5, 2)) AS frag
      , CAST(IPS.avg_page_space_used_in_percent AS decimal(5, 2)) AS page_usage
      , STATS_DATE(IPS.object_id, IPS.index_id) AS last_update
  FROM    sys.dm_db_index_physical_stats
      (
        DB_ID() -- Base de données
        , OBJECT_ID(@_table_name) -- Table
        , NULL -- Index
        , NULL -- Partition
        , 'DETAILED' -- Niveau de détail
      ) IPS
  LEFT JOIN  sys.dm_db_index_usage_stats AS IUS
        ON IUS.database_id = IPS.database_id
        AND IUS.object_id = IPS.object_id
        AND IUS.index_id = IPS.index_id
  INNER JOIN  sys.databases AS D
        ON D.database_id = IPS.database_id
  INNER JOIN  sys.indexes AS I
        ON I.object_id = IPS.object_id
        AND I.index_id = IPS.index_id
  INNER JOIN  sys.objects AS O
        ON O.object_id = IPS.object_id
  WHERE    IPS.index_level = 0
  AND    IPS.alloc_unit_type_desc <> 'LOB_DATA'
END