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

Frédéric BROUARD

Pour réagir au contenu de cet article, un espace de dialogue vous est proposé sur le forum. Commentez

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

- Auteur : Frédéric BROUARD

- Parution : 24 septembre 2022

- Public visé :

- Licence : Copyright ® 2022 Frédéric BROUARD. Aucune reproduction, même partielle, ne peut être faite de ce site et 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.

Sommaire

- I. L’auteur

- II. Introduction

- III. L’outil

- III-A. La fonction dbo.F_SPLIT_SEARCH

- III-B. La procédure dbo.sp__SEARCH_CODE_PATTERN

- IV. Remerciements Developpez.com

I - L’auteur

Blog :
https://blog.developpez.com/sqlpro
Le site :
https://sqlpro.developpez.com

II - Introduction

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 :

1. CREATE FUNCTION dbo.F_SPLIT_SEARCH

2. (@STR     NVARCHAR(max),  -- texte objet de la recherche du motif

3. @PATTERN NVARCHAR(256),  -- motif à chercher

4. @FENETRE TINYINT)        -- nombre de caractères avant et après à ajouter

5. RETURNS @T TABLE (STRING_FIND NVARCHAR(300),

6. POSITION    INT,

7. ORDINAL INT

8. )

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 :

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 :

1. CREATE PROCEDURE dbo.sp__SEARCH_CODE_PATTERN

2. @PATTERN   NVARCHAR(256),  -- motif recherché

3. @COLLATION VARCHAR(128),   -- collation désirée

4. @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.

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 :

1. USE master

2. GO

4. CREATE FUNCTION dbo.F_SPLIT_SEARCH

5. (@STR     NVARCHAR(max),  -- texte objet de la recherche du motif

6. @PATTERN NVARCHAR(256),  -- motif à chercher

7. @FENETRE TINYINT)        -- nombre de caractères avant et après à ajouter

8. RETURNS @T TABLE (STRING_FIND NVARCHAR(300), POSITION INT, ORDINAL INT)

9. AS

10. /******************************************************************************

11. * MODULE  : GREP code Transact SQL                                            *

12. * NATURE  : FONCTION                                                          *

13. * OBJECT  : dbo.F_SPLIT_SEARCH                                                *

14. * OUPUT   : table                                                             *

15. * CREATE  : 2022-09-14                                                        *

16. * AUTHOR  : Frédéric BROUARD - SQLpro - SARL SQL spot - sqlpro@sqlspot.com    *

17. * VERSION : 1                                                                 *

18. * VALID   : 2008...                                                           *

19. *******************************************************************************

20. * Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *

21. * Architecte de données :  expertise, audit, conseil, formation, modélisation *

22. * tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *

23. * blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *

24. * expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *

25. *******************************************************************************

26. * PURPOSE : découpe une chaine de caractères en de multiples portions         *

27. *           chacune contenant le motif recherché et indique la position       *

28. *******************************************************************************

29. * INPUTS  : @STR : la chaine dans laquelle s'effectue la recherche            *

30. *           @PATTERN : le motif recherché                                     *

31. *           @FENETRE : nombre de caractères à afficher avant et après         *

32. *******************************************************************************

33. * EXAMPLE : SELECT * FROM dbo.F_SPLIT_SEARCH ('rantanplan', 'an', 3);         *

34. *******************************************************************************

35. * IMPROVE : collation sensitivity                                             *

36. *******************************************************************************

37. * BUGFIX  :                                                                   *

38. ******************************************************************************/

39. BEGIN

40. -- variable de recherche :

41. DECLARE @I INT,              -- position du motif cherché

42. @LS INT

43. = LEN(@STR),      -- longueur de la chaine

44. @LP TINYINT

45. = LEN(@PATTERN),  -- longueur du motif

46. @AVANT INT,          -- position de début pour l'affichage

47. @APRES INT,          -- position de début pour l'affichage

48. @N INT = 1;          -- numéro ordinal de l'expression trouvée

49. SET @I = CHARINDEX(@PATTERN  COLLATE French_CI_AI, @STR);

50. WHILE @I > 0

51. -- tant que l'on trouve le motif

52. BEGIN

53. -- on récupère le début

54. SET @AVANT = @I - @FENETRE;

55. IF @AVANT < 1 -- on est au début de la chaine

56. SET @AVANT = 1;

57. -- on récupère la fin

58. SET @APRES = @I + @LP + @FENETRE;

59. IF @APRES > @LS

60. SET @APRES = @LS + 1; -- on est à la fin de la chaine

61. -- on insère la sous-chaine avec sa position ordinale

62. INSERT INTO @T

63. SELECT SUBSTRING(@STR, @AVANT, @APRES - @AVANT), @I, @N;

64. -- recherche de l'élément suivant et incrémentation de l'ordinal

65. SELECT @I = CHARINDEX(@PATTERN, @STR, @I + @LP - 1),

66. @N = @N + 1;

67. END;

68. RETURN

69. END;

70. GO

72. USE master

73. GO

75. CREATE PROCEDURE dbo.sp__SEARCH_CODE_PATTERN

76. @PATTERN   NVARCHAR(256),  -- motif recherché

77. @COLLATION VARCHAR(128),   -- collation désirée

78. @EXERGUE   BIT             -- si 1 mise en exergue du motif dans le retour

79. AS

80. /******************************************************************************

81. * MODULE  : GREP code Transact SQL                                            *

82. * NATURE  : PROCEDURE                                                         *

83. * OBJECT  : dbo.sp__SEARCH_CODE_PATTERN                                       *

84. * CREATE  : 2022-09-14                                                        *

85. * AUTHOR  : Frédéric BROUARD - SQLpro - SARL SQL spot - sqlpro@sqlspot.com    *

86. * VERSION : 1                                                                 *

87. * SYSTEM  : OUI                                                               *

88. * VALID   : 2008 ...                                                          *

89. *******************************************************************************

90. * Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *

91. * Architecte de données :  expertise, audit, conseil, formation, modélisation *

92. * tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *

93. * blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *

94. * expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *

95. *******************************************************************************

96. * PURPOSE : recherche tous les codes (vue sys.sql_modules) dont le texte      *

97. *           contient un certain motif dans toutes les bases actives           *

98. *******************************************************************************

99. * INPUTS  : @PATTERN : motif recherché                                        *

100. *           @COLLATION : collation pour contraindre la recherche              *

101. *           @EXERGUE : si 1 entoure le motif cherché de, --> et <--           *

102. *******************************************************************************

103. * EXAMPLE : EXEC dbo.sp__SEARCH_CODE_PATTERN 'IDENTITY', NULL, 1;             *

104. *******************************************************************************

105. * IMPROVE : permettre la recherche dans une seule base ou une liste de bases  *

106. *******************************************************************************

107. * BUGFIX  :                                                                   *

108. ******************************************************************************/

109. SET NOCOUNT ON;

110. SET @COLLATION = COALESCE(@COLLATION, 'Latin1_General_CI_AI');

111. -- on teste si la collation existe

112. IF NOT EXISTS(SELECT *

113. FROM   sys.fn_helpcollations()

114. WHERE name = @COLLATION)

115. BEGIN

116. 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);

117. RETURN;

118. END;

120. SET @EXERGUE = COALESCE(@EXERGUE, 0);

122. DECLARE @SQL NVARCHAR(max) = N'';

123. -- construction d'une chaine de caractères contenant la requête

124. -- parcourant toutes les bases

125. SELECT @SQL = @SQL

126. + 'SELECT ''' + name + ''' COLLATE ' + @COLLATION + ' AS DBNAME , '

127. + 'm.object_id, definition COLLATE ' + @COLLATION + ' AS def, '

128. + 's.name COLLATE ' + @COLLATION + ' AS OBJECT_SCHEMA, '

129. + 'o.name COLLATE ' + @COLLATION + ' AS OBJECT_NAME, '

130. + 'o.type_desc COLLATE ' + @COLLATION + ' AS OBJECT_TYPE FROM '

131. + name + '.sys.sql_modules AS m JOIN '

132. + name + '.sys.objects AS o ON m.object_id = o.object_id JOIN '

133. + name + '.sys.schemas AS s ON o.schema_id = s.schema_id UNION ALL '

134. FROM   sys.databases

135. WHERE  source_database_id IS NULL

136. AND  state = 0

137. AND  name NOT IN ('tempdb', 'model', 'master', 'msdb');

138. -- finalisation de la requête dynamique

139. -- pour rechercher le motif dans les codes Transact SQL

140. SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);

141. SET @SQL = 'WITH T AS (' + @SQL

142. + ') SELECT DBNAME, OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE, '

143. + ' REPLACE(STRING_FIND, ''' + @PATTERN + ''','''

144. + CASE @EXERGUE WHEN 1 THEN '-->' ELSE '' END + @PATTERN

145. + CASE @EXERGUE WHEN 1 THEN '<--' ELSE '' END

146. + ''') AS FIND, POSITION, ORDINAL FROM T CROSS APPLY '

147. + 'dbo.F_SPLIT_SEARCH(def,'''

148. + @PATTERN + ''', 10) WHERE def LIKE ''%' + @PATTERN + '%'';'

149. EXEC (@SQL);

150. GO

152. EXEC sp_MS_marksystemobject 'sp__SEARCH_CODE_PATTERN';

153. 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 :

1. USE master

2. GO

4. CREATE FUNCTION dbo.F_SPLIT_SEARCH

5. (@STR     NVARCHAR(max),  -- texte objet de la recherche du motif

6. @PATTERN NVARCHAR(256),  -- motif à chercher

7. @FENETRE TINYINT)        -- nombre de caractères avant et après à ajouter

8. RETURNS @T TABLE (STRING_FIND NVARCHAR(300), POSITION INT, ORDINAL INT)

9. AS

10. /******************************************************************************

11. * MODULE  : GREP code Transact SQL                                            *

12. * NATURE  : FONCTION                                                          *

13. * OBJECT  : dbo.F_SPLIT_SEARCH                                                *

14. * OUPUT   : table                                                             *

15. * CREATE  : 2022-09-14                                                        *

16. * AUTHOR  : Frédéric BROUARD - SQLpro - SARL SQL spot - sqlpro@sqlspot.com    *

17. * VERSION : 1                                                                 *

18. * VALID   : 2008...                                                           *

19. *******************************************************************************

20. * Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *

21. * Architecte de données :  expertise, audit, conseil, formation, modélisation *

22. * tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *

23. * blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *

24. * expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *

25. *******************************************************************************

26. * PURPOSE : découpe une chaine de caractères en de multiples portions         *

27. *           chacune contenant le motif recherché et indique la position       *

28. *******************************************************************************

29. * INPUTS  : @STR : la chaine dans laquelle s'effectue la recherche            *

30. *           @PATTERN : le motif recherché                                     *

31. *           @FENETRE : nombre de caractères à afficher avant et après         *

32. *******************************************************************************

33. * EXAMPLE : SELECT * FROM dbo.F_SPLIT_SEARCH ('rantanplan', 'an', 3);         *

34. *******************************************************************************

35. * IMPROVE : collation sensitivity                                             *

36. *******************************************************************************

37. * BUGFIX  :                                                                   *

38. ******************************************************************************/

39. BEGIN

40. -- variable de recherche :

41. DECLARE @I INT,              -- position du motif cherché

42. @LS INT

43. = LEN(@STR),      -- longueur de la chaine

44. @LP TINYINT

45. = LEN(@PATTERN),  -- longueur du motif

46. @AVANT INT,          -- position de début pour l'affichage

47. @APRES INT,          -- position de début pour l'affichage

48. @N INT = 1;          -- numéro ordinal de l'expression trouvée

49. SET @I = CHARINDEX(@PATTERN  COLLATE French_CI_AI, @STR);

50. WHILE @I > 0

51. -- tant que l'on trouve le " motif "

52. BEGIN

53. -- on récupère le début

54. SET @AVANT = @I - @FENETRE;

55. IF @AVANT < 1 -- on est au début de la chaine

56. SET @AVANT = 1;

57. -- on récupère la fin

58. SET @APRES = @I + @LP + @FENETRE;

59. IF @APRES > @LS

60. SET @APRES = @LS + 1; -- on est à la fin de la chaine

61. -- on insère la sous-chaine avec sa position ordinale

62. INSERT INTO @T

63. SELECT SUBSTRING(@STR, @AVANT, @APRES - @AVANT), @I, @N;

64. -- recherche de l'élément suivant et incrémentation de l'ordinal

65. SELECT @I = CHARINDEX(@PATTERN, @STR, @I + @LP - 1),

66. @N = @N + 1;

67. END;

68. RETURN

69. END;

70. GO

72. USE master

73. GO

75. CREATE PROCEDURE dbo.sp__SEARCH_CODE_PATTERN

76. @PATTERN   NVARCHAR(256),  -- motif recherché

77. @COLLATION VARCHAR(128),   -- collation désirée

78. @EXERGUE   BIT             -- si 1 mise en exergue du motif dans le retour

79. AS

80. /******************************************************************************

81. * MODULE  : GREP code Transact SQL                                            *

82. * NATURE  : PROCEDURE                                                         *

83. * OBJECT  : dbo.sp__SEARCH_CODE_PATTERN                                       *

84. * CREATE  : 2022-09-14                                                        *

85. * AUTHOR  : Frédéric BROUARD - SQLpro - SARL SQL spot - sqlpro@sqlspot.com    *

86. * VERSION : 1                                                                 *

87. * SYSTEM  : OUI                                                               *

88. * VALID   : 2008 ...                                                          *

89. *******************************************************************************

90. * Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *

91. * Architecte de données :  expertise, audit, conseil, formation, modélisation *

92. * tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *

93. * blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *

94. * expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *

95. *******************************************************************************

96. * PURPOSE : recherche tous les codes (vue sys.sql_modules) dont le texte      *

97. *           contient un certain motif dans toutes les bases actives           *

98. *******************************************************************************

99. * INPUTS  : @PATTERN : motif recherché                                        *

100. *           @COLLATION : collation pour contraindre la recherche              *

101. *           @EXERGUE : si 1 entoure le motif cherché de, --> et <--           *

102. *******************************************************************************

103. * EXAMPLE : EXEC dbo.sp__SEARCH_CODE_PATTERN 'IDENTITY', NULL, 1;             *

104. *******************************************************************************

105. * IMPROVE : permettre la recherche dans une seule base ou une liste de bases  *

106. *******************************************************************************

107. * BUGFIX  :                                                                   *

108. ******************************************************************************/

109. SET NOCOUNT ON;

110. SET @COLLATION = COALESCE(@COLLATION, 'Latin1_General_CI_AI');

111. -- on teste si la collation existe

112. IF NOT EXISTS(SELECT *

113. FROM   sys.fn_helpcollations()

114. WHERE name = @COLLATION)

115. BEGIN

116. 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);

117. RETURN;

118. END;

120. SET @EXERGUE = COALESCE(@EXERGUE, 0);

122. DECLARE @SQL NVARCHAR(max) = N'';

123. -- construction d'une chaine de caractères contenant la requête

124. -- parcourant toutes les bases

125. SELECT @SQL = @SQL

126. + 'SELECT ''' + name + ''' COLLATE ' + @COLLATION + ' AS DBNAME , '

127. + 'm.object_id, definition COLLATE ' + @COLLATION + ' AS def, '

128. + 's.name COLLATE ' + @COLLATION + ' AS OBJECT_SCHEMA, '

129. + 'o.name COLLATE ' + @COLLATION + ' AS OBJECT_NAME, '

130. + 'o.type_desc COLLATE ' + @COLLATION + ' AS OBJECT_TYPE FROM '

131. + name + '.sys.sql_modules AS m JOIN '

132. + name + '.sys.objects AS o ON m.object_id = o.object_id JOIN '

133. + name + '.sys.schemas AS s ON o.schema_id = s.schema_id UNION ALL '

134. FROM   sys.databases

135. WHERE  source_database_id IS NULL

136. AND  state = 0

137. AND  name NOT IN ('tempdb', 'model', 'master', 'msdb');

138. -- finalisation de la requête dynamique

139. -- pour rechercher le motif dans les codes Transact SQL

140. SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);

141. SET @SQL = 'WITH T AS (' + @SQL

142. + ') SELECT DBNAME, OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE, '

143. + ' REPLACE(STRING_FIND, ''' + @PATTERN + ''','''

144. + CASE @EXERGUE WHEN 1 THEN '-->' ELSE '' END + @PATTERN

145. + CASE @EXERGUE WHEN 1 THEN '<--' ELSE '' END

146. + ''') AS FIND, POSITION, ORDINAL FROM T CROSS APPLY '

147. + 'dbo.F_SPLIT_SEARCH(def,'''

148. + @PATTERN + ''', 10) WHERE def LIKE ''%' + @PATTERN + '%'';'

149. EXEC (@SQL);

150. GO

152. EXEC sp_MS_marksystemobject 'sp__SEARCH_CODE_PATTERN';

153. GO

155. /******************************************************************************

156. -- EXEMPLES :

158. EXEC sp__SEARCH_CODE_PATTERN 'CON', 'French_CS_AS', 0;

160. EXEC sp__SEARCH_CODE_PATTERN 'CON', NULL, 1;

162. EXEC sp__SEARCH_CODE_PATTERN 'CON', 'French_CS_AS', 1;

165. ******************************************************************************/

IV - Remerciements Developpez.com

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