Métadonnées des index (sp__helpindex)

La procédure stockée master.sys.sp_helpindex, décrivant les index d’une table, fournit par Microsoft, est aujourd’hui fortement obsolète et Microsoft n’a pas souhaité donné suite aux demandes de modification (1). Voici une procédure inspirée de cette dernière bien plus complète et prenant en compte tous les types d’index (spatiaux, XML, columstore, fulltext).

(1) Dans l’outil CONNECT consacré aux amélioration de SQL Server, il est dit par Microsoft, que, malgré les multiples demandes, la procédure stockée sp_helpindex, ne sera pas améliorée…
Cependant cette procédure est bien pratique pour comparer les index d’une même table afin de savoir s’il n’existe pas déjà tel ou tel index, au moment d’en créer de nouveaux, ou encore, s’il existe des index inclus ou redondants.
Voici donc une nouvelle procédure intitulée sp__helpindex (notez le double blanc souligné afin de ne pas écraser l’ancienne) qui permet de pallier à la déficience de l’ancienne et qui rajoute :

  • d’un point de vu « horizontal » : nom de table, colonnes incluses, filtre et l’essentiel de la clause WITH
  • d’un point de vue vertical : les index columnstore, xml, spatiaux et fulltext

Voici le code de cette procédure :

CREATE PROCEDURE dbo.sp__HELPINDEX @OBJ NVARCHAR(133)
AS
/******************************************************************************
* METADONNÉES DES INDEX                                                       *
* Procédure listant sous forme de tables les métadonnées logique des index    *
*******************************************************************************  
* Frédéric Brouard - SQLpro@SQLspot.com - Sté SQL SPOT http://www.sqlspot.com *
* Plus d'info. sur http://sqlpro.developpez.com   -  2017-01-17 - version 1.0 *  
*******************************************************************************  
* Cette procédure prend en argument le nom d'une table avec son schéma SQL    *
* (sinon le schéma par défaut de l'utilisateur qui la lance) et renvoie un    *
* jeu de données contenant le descriptif logique de constitution d'un index   *
* ou de tous les index de la base                                             *
* NOTA substitut à la procédure stockée système sp_helpindex                  *
*******************************************************************************
* ATTENTION  : procédure système ! Exécutable depuis n'importe quelle base    *
*                                                                             *
* Paramètre en entrée :                                                       *
*    @OBJ type NVARCHAR(133) : nom de table dont on veut connaître les index  *
*       si vide, la procédure renvoie la liste de tous les index              *
*                                                                             *
* COLONNE de la table en sortie :                                             *
*    TABLE_NAME : nom de la table en deux parties (schéma SQL + nom)          *
*    INDEX_NAME : nom de l'index (1)                                          *
*    INDEX TYPE : type d'index                                                *
*    INDEX_STORAGE : type et nom de l'espace de stockage                      *
*    INDEX_KEY : liste des colonnes composant la clef d'index (2)             *
*    INDEX_INCLUDE : liste des colonnes incluses (3)                          *
*    INDEX_FILTER : filtre d'index s'il y a lieu                              *
*    INDEX_WITH : principaux paramètres de la clause WITH                     *
*                                                                             *
* NOTA :                                                                      *
*   (1) certains index n'ont pas de nom. C'est le cas des index "fulltext"    *
*   (2) certains index n'ont pas de clef. C'est le cas des index              *
*       "columnstore" et "fulltext". Dans ce cas les colonnes indexées        *
*       figurent dans la clause INDEX_INCLUDE                                 *
*   (3) pour les index CLUSTERED de type BTree, toutes les colonnes autre     *
*       que celles de la clef étant présente, la valeur présentée est :       *
*       * - (INDEX_KEY)                                                       *
*       pour les index CLUSTERED COLUMNSTORE toutes les colonnes de la table  *
*       étant présente, la valeur présentée est : *                           *
*                                                                             *
* EXEMPLE :                                                                   *
*    EXEC dbo.sp__HELPINDEX NULL                                              *
*    ... donne la liste de tous les index de la base ...                      *
*    EXEC dbo.sp__HELPINDEX  '[client]'                                       *
*    ... donne la liste des index de la table client ...                      *
*    EXEC dbo.sp__HELPINDEX  'ventes.client'                                  *
*    ... donne la liste des index de la table client du schéma SQL vente...   *
*                                                                             *
******************************************************************************/


SET NOCOUNT ON;
WITH
T0 AS
(
SELECT i."type", s.name + '.' + o.name AS TABLE_NAME,
       i.name AS INDEX_NAME,
       CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
       CASE WHEN xi.xml_index_type = 0 THEN 'PRIMARY ' ELSE '' END +
       i.type_desc + CASE WHEN xi.xml_index_type > 0
                            THEN ' FOR ' + xi.secondary_type_desc ELSE ''
                     END AS INDEX_TYPE,
       COALESCE('FILEGROUP: ' + fg.name,
                'PARTITION: ' + ps.name COLLATE database_default +'('  
                  + STUFF((SELECT N', ' + cp.name  
                           FROM   sys.index_columns AS icp
                                    LEFT OUTER JOIN sys.COLUMNS AS cp
                                       ON icp.object_id = cp.object_id
                                          AND icp.column_id = cp.column_id
                           WHERE  icp.object_id = i.object_id
                                    AND icp.index_id = i.index_id  
                                    AND icp.partition_ordinal >= 1
                           ORDER  BY icp.partition_ordinal    
                           FOR XML PATH(N'')), 1, 1,'') +')' )
          AS INDEX_STORAGE,
       LTRIM(STUFF((SELECT N', '  + CONCAT(c.name, ' '
                                          + CASE
                                               WHEN i.type_desc
                                                    IN ('XML',
                                                        'SPATIAL',
                                                        'COLUMNSTORE')
                                                  THEN ''
                                               WHEN is_descending_key = 1
                                                  THEN 'DESC'
                                               WHEN is_descending_key = 0
                                                  THEN 'ASC'
                                               ELSE ''
                                            END)  
              FROM   sys.index_columns AS ic
                     JOIN sys.COLUMNS AS c
                          ON ic.object_id = c.object_id
                             AND ic.column_id = c.column_id
              WHERE  ic.object_id = i.object_id
                AND  ic.index_id = i.index_id
                AND  is_included_column = 0
              ORDER  BY key_ordinal    
              FOR XML PATH(N'')), 1, 1,'')) AS INDEX_KEY,
       LTRIM(STUFF((SELECT N', ' + c.name  
                    FROM   sys.index_columns AS ic
                           JOIN sys.COLUMNS AS c
                                ON ic.object_id = c.object_id
                                   AND ic.column_id = c.column_id
                    WHERE  ic.object_id = i.object_id
                      AND  ic.index_id = i.index_id
                      AND  is_included_column = 1
                    ORDER  BY c.name    
                    FOR XML PATH(N'')), 1, 1,'')) AS INDEX_INCLUDE,
       i.filter_definition AS INDEX_FILTER,
       'FILL_FACTOR = '      + CAST(i.fill_factor AS VARCHAR(32)) +
       ', PAD_INDEX = '      + CAST(i.is_padded AS CHAR(1)) +
       ', ALLOW_ROWLOCK = '  + CAST(i.allow_row_locks AS CHAR(1)) +
       ', ALLOW_PAGELOCK = ' + CAST(i.allow_page_locks AS CHAR(1))  
          AS INDEX_WITH
FROM   sys.indexes AS i
       JOIN sys.objects AS o
            ON i.object_id = o.object_id
            JOIN sys.schemas  AS s  
                 ON o.schema_id = s.schema_id
       JOIN sys.data_spaces AS ds
            ON i.data_space_id = ds.data_space_id
            LEFT OUTER JOIN sys.filegroups AS fg
                 ON ds.data_space_id = fg.data_space_id AND ds."type" = 'FG'
            LEFT OUTER JOIN sys.partition_schemes AS ps
                 ON ds.data_space_id = ps.data_space_id AND ds."type" = 'PS'
                 LEFT OUTER JOIN sys.partition_functions AS pf
                      ON ps.function_id = pf.function_id
       LEFT OUTER JOIN sys.xml_indexes AS xi
            ON i.object_id = xi.object_id
            AND i.index_id = xi.index_id
WHERE  o.object_id = COALESCE(NULLIF(OBJECT_ID(@OBJ), 0), o.object_id)
AND    i.index_id > 0
AND    o.is_ms_shipped = 0
)
SELECT TABLE_NAME, INDEX_NAME, INDEX_TYPE, INDEX_STORAGE, INDEX_KEY,
       CASE "type" WHEN 1 THEN '* - (INDEX_KEY)'
                   WHEN 5 THEN '*' END AS INDEX_INCLUDE,
       INDEX_FILTER, INDEX_WITH
FROM   T0
UNION ALL
SELECT s.name + '.' + o.name AS TABLE_NAME,
       NULL AS INDEX_NAME, 'FULL TEXT' AS INDEX_TYPE,
       'FULLTEXT CATALOG: ' + ftc.name AS INDEX_STORAGE,
       NULL AS INDEX_KEY,        
       LTRIM(STUFF((SELECT N', ' + c.name  
                    FROM   sys.fulltext_index_columns AS ftic
                           JOIN sys.COLUMNS AS c
                                ON c.object_id = ftic.object_id
                                   AND c.column_id = ftic.column_id
                    WHERE  ftic.object_id = fti.object_id    
                    FOR XML PATH(N'')), 1, 1,'')) AS INDEX_INCLUDE,
       NULL AS INDEX_FILTER,
       'CHANGE_TRACKING = ' + change_tracking_state_desc +
       ', STOPLIST = ' + CASE
                            WHEN fti.stoplist_id = 0 THEN 'SYSTEM'
                            WHEN fti.stoplist_id IS NULL THEN 'OFF'
                            ELSE (SELECT name
                                  FROM   sys.fulltext_stoplists AS ftsl
                                  WHERE  fti.stoplist_id = ftsl.stoplist_id)
                         END +
       COALESCE(', SEARCH PROPERTY LIST = ' +
                (SELECT name
                 FROM   sys.registered_search_property_lists AS ftpl
                 WHERE  fti.property_list_id = ftpl.property_list_id)
                    COLLATE French_BIN, '') AS INDEX_WITH
FROM   sys.fulltext_indexes AS fti
       JOIN sys.objects AS o
            ON fti.object_id = o.object_id
            JOIN sys.schemas AS s  
                 ON o.schema_id = s.schema_id
       JOIN sys.fulltext_catalogs AS ftc
            ON fti.fulltext_catalog_id = ftc.fulltext_catalog_id
WHERE  o.object_id = COALESCE(NULLIF(OBJECT_ID(@OBJ), 0), o.object_id)
ORDER  BY TABLE_NAME, INDEX_KEY, INDEX_INCLUDE;  
GO

Il convient de la créer dans la base master et de la rendre procédure système, à l’aide de la commande :

EXEC sp_MS_marksystemobject 'sp__HELPINDEX';

Les colonnes du jeu de données en sortie de procédure sont :

  • TABLE_NAME : nom de la table en deux parties (schéma SQL + nom)
  • INDEX_NAME : nom de l’index. Certains index n’ont pas de nom. C’est en particulier le cas des index « fulltext »
  • INDEX TYPE : type d’index
  • INDEX_STORAGE : type et nom de l’espace de stockage
  • INDEX_KEY : liste des colonnes composant la clef d’index. Certains index n’ont pas de clef. C’est le cas des index « columnstore » et « fulltext ». Dans ce cas les colonnes indexées figurent dans la clause INDEX_INCLUDE
  • INDEX_INCLUDE : liste des colonnes incluses. Pour les index CLUSTERED de type BTree, toutes les colonnes autre que celles de la clef étant présente, la valeur qui y figure est : * – (INDEX_KEY). Pour les index CLUSTERED COLUMNSTORE toutes les colonnes de la table étant présente dans l’index, la valeur qui y figure est : *
  • INDEX_FILTER : filtre d’index s’il y a lieu
  • INDEX_WITH : principaux paramètres de la clause WITH

Si vous l’exécutez sans spécifier le nom d’une table (le paramètre sera donc NULL) alors la procédure sort la liste des tous les index de toutes les tables, vues indexées comprises.

Vous pouvez transformer aisément cette procédure en une fonction table en ligne avantageuse.

LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE
Le code !

Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert  S.G.B.D  relationnelles   et   langage  S.Q.L
Moste  Valuable  Professionnal  Microsoft  SQL Server
Société SQLspot  :  modélisation, conseil, formation,
optimisation,  audit,  tuning,  administration  SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.

L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR

MVP Microsoft SQL
Server

Développez et administrez pour la performance avec SQL Server 2014

Développez et administrez pour la performance avec SQL Server 2014

Laisser un commentaire