IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

SQL Server - Un « GREP » pour rechercher un motif dans tous les codes Transact SQL

Pour réagir au contenu de cet article, un espace de dialogue vous est proposé sur le forum. Commentez Donner une note à l´article (5)

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. L’auteur

II. Introduction

Image non disponible

Il est parfois nécessaire de rechercher dans toute l’étendue du code Transact SQL (vues, procédures stockées, fonctions UDF, déclencheurs…) un motif de chaine de caractères (par exemple un nom de table, de colonne… par exemple afin de savoir quel va être l’impact d’un changement de structure d’un objet sur le code existant dans la base.

Cet article présente un outil permettant d’effectuer cette recherche.

III. L’outil

Il est composé de deux objets :

  • une fonction qui recherche dans le texte toutes les occurrences d’une sous-chaine et renvoie une table ;
  • une procédure qui effectue cette recherche sur tous les objets de toutes les bases du serveur sauf dans les bases système msdb, model, master et tempdb et les clichés.

III-A. La fonction dbo.F_SPLIT_SEARCH

Cette fonction recherche dans le texte (paramètres @STR de type NVARCHAR(max)) toutes les occurrences de la sous-chaine (@PATTERN de type NVARCHAR(256)) dans une chaine passée en paramètre et renvoie une table composée des colonnes :

  • STRING_FIND : extrait de la chaine recherchée ;
  • POSITION : position du premier caractère de la chaine recherchée dans le texte ;
  • ORDINAL : numéro séquentiel de l’extrait trouvé.

Pour que l’extrait soit compréhensible, le paramètre @FENETRE (de type TINIYINT) permet de rajouter devant et derrière la chaine recherchée autant de caractères que la valeur de l’argument @FENETRE.

La signature de la fonction est la suivante :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
CREATE FUNCTION dbo.F_SPLIT_SEARCH 
   (@STR     NVARCHAR(max),  -- texte objet de la recherche du motif
    @PATTERN NVARCHAR(256),  -- motif à chercher
    @FENETRE TINYINT)        -- nombre de caractères avant et après à ajouter
RETURNS @T TABLE (STRING_FIND NVARCHAR(300), 
                  POSITION    INT, 
                  ORDINAL INT
                 )

Exemple – recherche de la sous-chaine « on » dans la première strophe du poème « Chanson d’automne » de Paul Verlaine, en encadrant les motifs trouvés des 3 caractères précédents et suivants :

Image non disponible

Remarquez que dans la colonne STRING_FIND, le motif cherché « on » est encadré par les 3 caractères précédents et suivants… La colonne POSITION donne la position du premier caractère du motif cherché dans le texte, et la colonne ORDINAL, le numéro de séquence du motif trouvé dans le texte.

III-B. La procédure dbo.sp__SEARCH_CODE_PATTERN

Celle-ci recherche le motif dans tous les codes Transact SQL de toutes les bases, sauf bases système, et ce dans les différents objets et en extrait les différentes occurrences en utilisant la fonction table précédemment décrite.

La signature de cette procédure est la suivante :

 
Sélectionnez
1.
2.
3.
4.
CREATE PROCEDURE dbo.sp__SEARCH_CODE_PATTERN
   @PATTERN   NVARCHAR(256),  -- motif recherché
   @COLLATION VARCHAR(128),   -- collation désirée
   @EXERGUE   BIT             -- si 1 mise en exergue du motif dans le retour

Et elle renvoie une table composée des colonnes suivantes :

  • DBNAME : nom de la base de données ;
  • OBJECT_SCHEMA : nom du schéma SQL dans lequel se trouve l’objet ;
  • OBJECT_NAME : nom de l’objet ;
  • OBJECT_TYPE : type d’objet ;
  • FIND : extrait de la chaine recherchée ;
  • POSITION : position du premier caractère de la chaine recherchée dans le code de l’objet ;
  • ORDINAL : numéro séquentiel de l’extrait trouvé dans le code de l’objet.

Exemple – recherche du motif « IDENTITY » sans précision de la collation avec mise en exergue.

Image non disponible

Ces deux objets de code doivent être créés dans la base master et la procédure doit être marquée comme « système » avec l’exécution de la commande :

EXEC sp_MS_marksystemobject 'sp__SEARCH_CODE_PATTERN';

Voici l’ensemble du code de ces deux objets :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
USE master
GO
 
CREATE FUNCTION dbo.F_SPLIT_SEARCH 
   (@STR     NVARCHAR(max),  -- texte objet de la recherche du motif
    @PATTERN NVARCHAR(256),  -- motif à chercher
    @FENETRE TINYINT)        -- nombre de caractères avant et après à ajouter
RETURNS @T TABLE (STRING_FIND NVARCHAR(300), POSITION INT, ORDINAL INT)
AS
/******************************************************************************
* MODULE  : GREP code Transact SQL                                            *
* NATURE  : FONCTION                                                          *
* OBJECT  : dbo.F_SPLIT_SEARCH                                                *
* OUPUT   : table                                                             *
* CREATE  : 2022-09-14                                                        *
* AUTHOR  : Frédéric BROUARD - SQLpro - SARL SQL spot - sqlpro@sqlspot.com    *
* VERSION : 1                                                                 *
* VALID   : 2008...                                                           *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *
*******************************************************************************
* PURPOSE : découpe une chaine de caractères en de multiples portions         *
*           chacune contenant le motif recherché et indique la position       *
*******************************************************************************
* INPUTS  : @STR : la chaine dans laquelle s'effectue la recherche            *
*           @PATTERN : le motif recherché                                     *  
*           @FENETRE : nombre de caractères à afficher avant et après         *
*******************************************************************************
* EXAMPLE : SELECT * FROM dbo.F_SPLIT_SEARCH ('rantanplan', 'an', 3);         *
*******************************************************************************
* IMPROVE : collation sensitivity                                             *
*******************************************************************************
* BUGFIX  :                                                                   *
******************************************************************************/
BEGIN
-- variable de recherche :
   DECLARE @I INT,              -- position du motif cherché
           @LS INT 
              = LEN(@STR),      -- longueur de la chaine
           @LP TINYINT 
              = LEN(@PATTERN),  -- longueur du motif  
           @AVANT INT,          -- position de début pour l'affichage
           @APRES INT,          -- position de début pour l'affichage
           @N INT = 1;          -- numéro ordinal de l'expression trouvée     
   SET @I = CHARINDEX(@PATTERN  COLLATE French_CI_AI, @STR);
   WHILE @I > 0
   -- tant que l'on trouve le motif
   BEGIN
      -- on récupère le début
      SET @AVANT = @I - @FENETRE;
      IF @AVANT < 1 -- on est au début de la chaine
         SET @AVANT = 1;
      -- on récupère la fin
      SET @APRES = @I + @LP + @FENETRE;
      IF @APRES > @LS 
         SET @APRES = @LS + 1; -- on est à la fin de la chaine
      -- on insère la sous-chaine avec sa position ordinale
      INSERT INTO @T 
      SELECT SUBSTRING(@STR, @AVANT, @APRES - @AVANT), @I, @N;
      -- recherche de l'élément suivant et incrémentation de l'ordinal
      SELECT @I = CHARINDEX(@PATTERN, @STR, @I + @LP - 1), 
             @N = @N + 1;
   END;
   RETURN
END;
GO
 
USE master
GO
 
CREATE PROCEDURE dbo.sp__SEARCH_CODE_PATTERN
   @PATTERN   NVARCHAR(256),  -- motif recherché
   @COLLATION VARCHAR(128),   -- collation désirée
   @EXERGUE   BIT             -- si 1 mise en exergue du motif dans le retour
AS
/******************************************************************************
* MODULE  : GREP code Transact SQL                                            *
* NATURE  : PROCEDURE                                                         *
* OBJECT  : dbo.sp__SEARCH_CODE_PATTERN                                       *
* CREATE  : 2022-09-14                                                        *
* AUTHOR  : Frédéric BROUARD - SQLpro - SARL SQL spot - sqlpro@sqlspot.com    *
* VERSION : 1                                                                 *
* SYSTEM  : OUI                                                               *
* VALID   : 2008 ...                                                          *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *
*******************************************************************************
* PURPOSE : recherche tous les codes (vue sys.sql_modules) dont le texte      *
*           contient un certain motif dans toutes les bases actives           *
*******************************************************************************
* INPUTS  : @PATTERN : motif recherché                                        *
*           @COLLATION : collation pour contraindre la recherche              *
*           @EXERGUE : si 1 entoure le motif cherché de, --> et <--           *   
*******************************************************************************
* EXAMPLE : EXEC dbo.sp__SEARCH_CODE_PATTERN 'IDENTITY', NULL, 1;             *
*******************************************************************************
* IMPROVE : permettre la recherche dans une seule base ou une liste de bases  *
*******************************************************************************
* BUGFIX  :                                                                   *
******************************************************************************/
SET NOCOUNT ON;
SET @COLLATION = COALESCE(@COLLATION, 'Latin1_General_CI_AI');
-- on teste si la collation existe
IF NOT EXISTS(SELECT * 
              FROM   sys.fn_helpcollations() 
              WHERE name = @COLLATION)
BEGIN
   RAISERROR('La collation %s n''existe pas dans la liste des collations disponibles. Choisissez votre collation dans la liste résultante de la requête : " SELECT * FROM sys.fn_helpcollations();" .', 16, 1, @COLLATION);
   RETURN;
END;
 
SET @EXERGUE = COALESCE(@EXERGUE, 0);
 
DECLARE @SQL NVARCHAR(max) = N'';
-- construction d'une chaine de caractères contenant la requête
-- parcourant toutes les bases
SELECT @SQL = @SQL 
       + 'SELECT ''' + name + ''' COLLATE ' + @COLLATION + ' AS DBNAME , '
       + 'm.object_id, definition COLLATE ' + @COLLATION + ' AS def, '
       + 's.name COLLATE ' + @COLLATION + ' AS OBJECT_SCHEMA, '
       + 'o.name COLLATE ' + @COLLATION + ' AS OBJECT_NAME, '
       + 'o.type_desc COLLATE ' + @COLLATION + ' AS OBJECT_TYPE FROM '
       + name + '.sys.sql_modules AS m JOIN '
       + name + '.sys.objects AS o ON m.object_id = o.object_id JOIN '
       + name + '.sys.schemas AS s ON o.schema_id = s.schema_id UNION ALL '
FROM   sys.databases
WHERE  source_database_id IS NULL
  AND  state = 0
  AND  name NOT IN ('tempdb', 'model', 'master', 'msdb');
-- finalisation de la requête dynamique 
-- pour rechercher le motif dans les codes Transact SQL
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);
SET @SQL = 'WITH T AS (' + @SQL 
    + ') SELECT DBNAME, OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE, '
    + ' REPLACE(STRING_FIND, ''' + @PATTERN + ''','''
    + CASE @EXERGUE WHEN 1 THEN '-->' ELSE '' END + @PATTERN 
    + CASE @EXERGUE WHEN 1 THEN '<--' ELSE '' END
    + ''') AS FIND, POSITION, ORDINAL FROM T CROSS APPLY '
    + 'dbo.F_SPLIT_SEARCH(def,'''
    + @PATTERN + ''', 10) WHERE def LIKE ''%' + @PATTERN + '%'';'
EXEC (@SQL);
GO
 
EXEC sp_MS_marksystemobject 'sp__SEARCH_CODE_PATTERN';
GO

Voici quelques exemples de recherches :

  1. EXEC sp__SEARCH_CODE_PATTERN 'CON', 'French_CS_AS', 0;
  2. EXEC sp__SEARCH_CODE_PATTERN 'CON', NULL, 1;
  3. EXEC sp__SEARCH_CODE_PATTERN 'CON', 'French_CS_AS', 1;

Et le script Transact SQL contenant le tout :

 
Cacher/Afficher le codeSélectionnez

IV. Remerciements Developpez.com

Nous tenons à remercier escartefigue pour la relecture orthographique de cet article, et Malick pour la mise au gabarit.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Copyright © 2022 Frédéric BROUARD. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.