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 EXEC sp_MS_marksystemobject 'sp__HELPINDEX';