-----------------------------------------
-- 28/05/2012 - Copyright Nicolas Souquet
-----------------------------------------
 
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
 
------------------------------------------------------------------------------------------------------------------------------
DECLARE  @schema_name sysname = 'dbo'
  , @table_or_indexed_view_name sysname = 'maTable'
  , @statistic_name sysname = NULL -- NULL effectue l'étude pour toutes les statistiques ou index de la table
  ---
  , @index_study bit = 1 -- mettre à 1 pour indiquer si l'on souhaite regarder la fragmentation des index, 0 sinon
  , @index_scan_level_mode varchar(8) = 'LIMITED' -- scan level mode (DETAILED, SAMPLED, LIMITED)  
  ---
  , @separator varchar(2) = ', '
------------------------------------------------------------------------------------------------------------------------------
-- NE RIEN CHANGER APRÈS CETTE LIGNE
------------------------------------------------------------------------------------------------------------------------------
IF (SELECT OBJECT_ID('TEMPDB.dbo.#STUDY')) IS NOT NULL
BEGIN
  DROP TABLE #STUDY
END
 
------------------------------------------------------------------------------
-- Vérifie que la table ou vue indexée existe dans la base de données en cours
------------------------------------------------------------------------------
DECLARE @object_type char(1)
 
SELECT    @object_type = O.type
FROM    sys.schemas AS S
INNER JOIN  sys.objects AS O ON S.schema_id = O.schema_id
WHERE    S.name = @schema_name
AND    O.name = @table_or_indexed_view_name
AND    O.type IN ('U', 'V') -- table ou vue (indexée)
 
IF @object_type IS NULL
BEGIN
  RAISERROR('La table ou vue indexée ''%s.%s'' n''existe pas !', 16, 1, @schema_name, @table_or_indexed_view_name)
  RETURN
END
 
-- Seules les vues indexées on des statistiques
IF @object_type = 'V'
AND NOT EXISTS
(
  SELECT    *
  FROM    sys.schemas AS S
  INNER JOIN  sys.views AS V ON S.schema_id = V.schema_id
  INNER JOIN  sys.indexes AS I ON V.object_id = I.object_id
  WHERE    S.name = @schema_name
  AND    V.name = @table_or_indexed_view_name
)
BEGIN
  RAISERROR('The %s.%s view is not indexed !', 16, 1, @schema_name, @table_or_indexed_view_name)
  RETURN
END
 
DECLARE @qualified_table_or_indexed_view_name nvarchar(256) = @schema_name + '.' + @table_or_indexed_view_name
 
--------------------------------------------------
-- Collecte des statistiques de colonne et d'index
--------------------------------------------------
DECLARE  @stat_header TABLE
(
  stat_name varchar(256)
  , updated varchar(32)
  , row_count bigint
  , rows_sampled bigint
  , steps int
  , density float
  , avg_key_length float
  , string_index char(3)
  , filter_expression varchar(1024)  
  , unfiltered_rows bigint
)
 
DECLARE @density_vector TABLE
(
  all_density float
  , average_length float
  , column_list varchar(1024)
)
 
DECLARE @stat_name_density_vector TABLE
(
  stat_name varchar(256)
  , all_density float
)
 
DECLARE @stat_name varchar(256) = ' '
  , @object_id int
  , @sql_stat_header nvarchar(256) = 'DBCC SHOW_STATISTICS(@TABLE@, @STATS@) WITH STAT_HEADER, NO_INFOMSGS'
  , @sql_density_vector nvarchar(256) = 'DBCC SHOW_STATISTICS(@TABLE@, @STATS@) WITH DENSITY_VECTOR, NO_INFOMSGS'
  , @exec_string nvarchar(512)
 
WHILE DATALENGTH(@stat_name) > 0
BEGIN
  SET @stat_name = NULL
 
  SELECT    TOP(1) @stat_name = QUOTENAME(D.name)
  FROM    sys.stats AS D
  INNER JOIN  sys.objects AS O ON D.object_id = O.object_id
  INNER JOIN  sys.schemas AS S ON O.schema_id = S.schema_id
  LEFT JOIN  @stat_header AS SH ON D.name = SH.stat_name
  WHERE    S.name = @schema_name
  AND    O.name = @table_or_indexed_view_name
  AND    SH.stat_name IS NULL
  AND    (@statistic_name IS NULL OR D.name = @statistic_name)
 
  -- Statistics header
  SELECT @exec_string = REPLACE(REPLACE(@sql_stat_header, '@STATS@', @stat_name), '@TABLE@', '''' + @schema_name + '.' + @table_or_indexed_view_name  + '''')
 
  INSERT  @stat_header
  EXEC  (@exec_string)
 
  -- Density vector
  SELECT @exec_string = REPLACE(REPLACE(@sql_density_vector, '@STATS@', @stat_name), '@TABLE@', '''' + @schema_name + '.' + @table_or_indexed_view_name  + '''')
 
  INSERT  @density_vector (all_density, average_length, column_list)
  EXEC  (@exec_string)
 
  INSERT  INTO @stat_name_density_vector
  SELECT  TOP 1 @stat_name
    , all_density
  FROM  @density_vector
  ORDER  BY average_length
 
  DELETE  FROM @density_vector
END
-----------------------------------------------------------------------------------------------------------------------
;WITH
  INDEX_CTE AS
  (
    SELECT    DISTINCT I.name AS index_name
        , I.index_id
        , PS.partition_number
        , PS.row_count
        , I.type_desc
        , I.is_unique
        , I.is_unique_constraint
        , I.is_primary_key
        , LEFT(KC.key_column_list, LEN(KC.key_column_list) - LEN(@separator)) AS key_column_list
        , LEFT(KCI.included_column_list, LEN(KCI.included_column_list) - 1) AS included_column_list
        , REPLACE(REPLACE(REPLACE(REPLACE(I.filter_definition, '[', ''), ']', ''), '(', ''), ')', '') AS filter_definition
        , 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.user_updates
        , LS.max_last_search_date
        , I.is_disabled  
        , (PS.reserved_page_count * 8192) / 1024 AS index_size_kb
        , IPS.index_level
        , IPS.index_depth
        , CAST(IPS.avg_fragmentation_in_percent AS decimal(5, 2)) AS avg_frag_pct
        , I.fill_factor
        , IPS.forwarded_record_count
    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()
    LEFT JOIN  (
          SELECT  object_id
            , index_id
            , database_id
            , index_level
            , index_depth
            , avg_fragmentation_in_percent
            , forwarded_record_count
          FROM  sys.dm_db_index_physical_stats
            (
              DB_ID() -- database
              , OBJECT_ID(@table_or_indexed_view_name) -- all tables
              , NULL -- all indexes
              , NULL -- all partitions
              , @index_scan_level_mode -- scan level mode (DETAILED, SAMPLED, LIMITED)
            )
          WHERE  @index_study = 1
        ) AS IPS
        ON IPS.object_id = I.object_id
        AND IPS.index_id = I.index_id
        AND IPS.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 + CASE ICS.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END + @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_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_indexed_view_name IS NULL OR O.name = @table_or_indexed_view_name)
    AND    (@stat_name IS NULL OR I.name = @stat_name)
  )
SELECT    COALESCE(C.type_desc, 'STAT') AS type_desc
    , CAST(SH.density AS decimal(7,6)) AS header
    , SNDV.all_density AS vector
    , C.row_count
    , SH.stat_name
    , COALESCE(C.key_column_list, LEFT(SC.stat_column_list, LEN(SC.stat_column_list) - 1)) AS stat_column_list
    , C.included_column_list
    , C.index_id
    , C.partition_number
    , C.is_disabled
    , C.is_unique AS is_uq
    , C.is_unique_constraint AS is_uqc
    , C.is_primary_key AS is_PK
    , REPLACE(REPLACE(REPLACE(REPLACE(SH.filter_expression, '[', ''), ']', ''), '(', ''), ')', '') AS filter_definition
    , SH.row_count AS stat_row_count
    , COALESCE(C.row_count, SH.row_count) AS real_row_count
    , SI.rowmodctr
    , SH.rows_sampled
    , C.index_size_kb
    , STATS_DATE(D.object_id, D.stats_id) AS last_stat_update
    , C.user_seeks
    , C.user_scans
    , C.user_lookups    
    , C.total_user_searches
    , C.user_updates
    , C.max_last_search_date
    , C.index_depth
    , C.avg_frag_pct
    , C.index_level
    , CASE
      WHEN COALESCE(C.type_desc, 'STAT') = 'STAT' THEN NULL
      WHEN C.index_level = 0 THEN 'LEAF'
      WHEN C.index_depth - C.index_level > 1 THEN 'INTERMEDIATE'
      ELSE 'ROOT'
    END AS index_level_type
    , C.forwarded_record_count
INTO    #STUDY
FROM    @stat_header AS SH
INNER JOIN  @stat_name_density_vector AS SNDV
      ON QUOTENAME(SH.stat_name) = SNDV.stat_name
LEFT JOIN  INDEX_CTE AS C
      ON SH.stat_name = C.index_name
INNER JOIN  sys.stats AS D
      ON D.object_id = OBJECT_ID(@schema_name + '.' + @table_or_indexed_view_name)
      AND D.name = SH.stat_name
INNER JOIN  sys.sysindexes AS SI
      ON SI.id = D.object_id
      AND SI.indid = D.stats_id
CROSS APPLY  (
      SELECT    C.name + ','
      FROM    sys.stats AS S
      INNER JOIN  sys.stats_columns AS SC
            ON S.object_id = SC.object_id
            AND S.stats_id = SC.stats_id
      INNER JOIN  sys.columns AS C
            ON C.object_id = SC.object_id
            AND C.column_id = SC.column_id
      WHERE    SH.stat_name = S.name
      ORDER BY  SC.stats_column_id
      FOR XML PATH ('')
    ) AS SC (stat_column_list)
ORDER BY  stat_name
 
SELECT  *
FROM  #STUDY
ORDER  BY type_desc