USE master;

DROP PROCEDURE dbo.sp__HELPINDEX;

GO



--===========================================================================--

-- METADONNÉES DES INDEX                                                     --

--===========================================================================--

-- Fredéric Brouard alias SQLpro                http://sqlpro.developpez.com --

-- Société SQL SPOT - http://www.sqlspot.com        2017-01-17 - version 1.0 --

--===========================================================================--





--▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄--

-- PHASE 1 : création de la procédure                                        --

--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--





USE master;

GO



CREATE PROCEDURE dbo.sp__HELPINDEX @OBJ NVARCHAR(133) = NULL 

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





EXEC sp_MS_marksystemobject 'sp__HELPINDEX';