I. L’auteur▲
|
|
Blog : |
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 :
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 :
|
|
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 :
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.
|
|
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 :
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 :
-
EXECsp__SEARCH_CODE_PATTERN'CON','French_CS_AS',0; -
EXECsp__SEARCH_CODE_PATTERN'CON',NULL,1; -
EXECsp__SEARCH_CODE_PATTERN'CON','French_CS_AS',1;
Et le script Transact SQL contenant le tout :
IV. Remerciements Developpez.com▲
Nous tenons à remercier escartefigue pour la relecture orthographique de cet article, et Malick pour la mise au gabarit.