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.
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èmes msdb, model, master et tempdb et les clichés
1 – 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é en paramètre et renvoie une table composée des colonnes :
- STRING_FIND : extrait de la chaine recherchée
- POSITION : position du premier caractères 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ère que la valeur de l’argument @FENETRE .
La signature de la fonction est la suivante :
CREATE FUNCTION dbo.F_SPLIT_SEARCH (@STR NVARCHAR(max), -- texte objet de la recehrche 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é 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ères du motif cherché dans le texte et la colonnes ORDINAL, le numéro de séquence du motif trouvé dans le texte.
2 – La procédure dbo.sp__SEARCH_CODE_PATTERN
Celle-ci recherche dans tous les codes Transact SQL de toutes les bases, sauf bases systèmes, le motif 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 :
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ères 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 :
USE master GO CREATE FUNCTION dbo.F_SPLIT_SEARCH (@STR NVARCHAR(max), -- texte objet de la recehrche 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 recherche 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'); -- finalisationn 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 :
EXEC sp__SEARCH_CODE_PATTERN 'CON', 'French_CS_AS', 0; EXEC sp__SEARCH_CODE_PATTERN 'CON', NULL, 1; EXEC sp__SEARCH_CODE_PATTERN 'CON', 'French_CS_AS', 1;
Et le script Transact SQL contenant le tout :
Code Transact SQL GREP finalisé
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *