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

/******************************************************************************
-- EXEMPLES :

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;


******************************************************************************/