CREATE OR ALTER PROCEDURE dbo.P_GREP 
   @DATA VARCHAR(128),     -- searched value
   @PARALLELIZE TINYINT,   -- number of procedures to be launched in parallel
   @DIVIDE TINYINT         -- remainder of div. indicating parallel filtering
AS
/******************************************************************************
* MODULE  : GREP                                                              *
* NATURE  : PROCEDURE                                                         *
* OBJECT  : dbo.P_GREP                                                        *
* CREATE  : 2024-01-13                                                        *
* AUTHOR  : Frédéric BROUARD (SQLpro)                                         *
* VERSION : 1                                                                 *
* SYSTEM  : NO                                                                *
* 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 : search for a literal value in all tables and all columns of a DB  *
*******************************************************************************
* INPUTS  : @DATA literal of the searched value                               *
            @PARALLELIZE TINYINT number of procedures to run in parallel      *
            @DIVIDE remainder of div. indicating parallel filtering           *
*******************************************************************************
* EXAMPLE : EXEC dbo.P_GREP 'mars', 3, 0;                                     *
*           EXEC dbo.P_GREP 'mars', 3, 1;                                     *
*           EXEC dbo.P_GREP 'mars', 3, 2;                                     *
* this will run the procedure 3 times in // each searching 1/3 of the tables  *
*******************************************************************************
* IMPROVE : 1) pass the DB name as an argument and make the proc as system    *
*           2) search of multiples values (horizontal or vertical)            *
*******************************************************************************
* BUGFIX  :                                                                   *
******************************************************************************/
SET NOCOUNT ON;

-- control phase

IF @DATA IS NULL
BEGIN
   RAISERROR('This procedure was not created to find NULL markers...', 16, 1);
   RETURN;
END;

IF @PARALLELIZE IS NULL OR @PARALLELIZE = 0
   SET @DIVIDE = NULL;

IF @DIVIDE IS NULL 
   SET @PARALLELIZE = NULL;

IF @PARALLELIZE IS NOT NULL 
   AND @DIVIDE NOT BETWEEN 0 AND @PARALLELIZE-1
BEGIN
   RAISERROR('Parallelizing the process needs to have a divide factor between 
             0 and @PARALLELIZE value minus 1. The values passed are %d for 
             @PARALLELIZE and %d for @DIVIDE.', 16, 1, @PARALLELIZE, @DIVIDE);
   RETURN;
END;

DECLARE @SQL NVARCHAR(max) = N''

-- cleaning phase

IF EXISTS(SELECT * 
          FROM   tempdb.INFORMATION_SCHEMA.TABLES 
          WHERE  TABLE_NAME = '#T_P_GREP')
   EXEC ('DROP TABLE #T_P_GREP;');

-- creating table phase

CREATE TABLE #T_P_GREP 
(GRP_ID     INT IDENTITY PRIMARY KEY,
 GRP_SCHEMA sysname,
 GRP_TABLE  sysname,
 GRP_COLUMN sysname,
 GRP_QUERY  NVARCHAR(1000),
 GRP_SEARCH BIT DEFAULT 0,
 GRP_FOUND  BIT DEFAULT 0);

CREATE TABLE #T_P_GREP_TEST 
(GPTP_INT  INT);

-- adding rows to the search table

INSERT INTO #T_P_GREP (GRP_SCHEMA, GRP_TABLE, GRP_COLUMN, GRP_QUERY)
SELECT C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME,
       N'SELECT * FROM ' 
       + QUOTENAME(C.TABLE_SCHEMA) + N'.' + QUOTENAME(C.TABLE_NAME) 
       + N' WITH(NOLOCK) WHERE ' +
       CASE DATA_TYPE 
          WHEN N'text' 
             THEN N'CAST(' + QUOTENAME(C.COLUMN_NAME)  + N' AS VARCHAR(max))'
          WHEN N'ntext' 
             THEN N'CAST(' + QUOTENAME(C.COLUMN_NAME)  + N' AS NVARCHAR(max))'
          WHEN N'xml' 
             THEN N'CAST(' + QUOTENAME(C.COLUMN_NAME)  + N' AS NVARCHAR(max))'
          ELSE QUOTENAME(C.COLUMN_NAME)
       END + N' LIKE ''%' + @DATA + N'%'''
FROM   INFORMATION_SCHEMA.COLUMNS AS C
       JOIN INFORMATION_SCHEMA.TABLES AS T 
          ON     C.TABLE_SCHEMA = T.TABLE_SCHEMA
             AND C.TABLE_NAME = T.TABLE_NAME
WHERE  TABLE_TYPE = 'BASE TABLE' -- uniquement les tables utilisateur
       AND (CHARACTER_MAXIMUM_LENGTH = -1 
            OR CHARACTER_MAXIMUM_LENGTH >= LEN(@DATA))
       AND (DATA_TYPE LIKE '%char' 
            OR DATA_TYPE LIKE '%text' 
            OR DATA_TYPE = 'xml') -- littéraux
       AND (@PARALLELIZE IS NULL 
           OR ABS(CHECKSUM(C.TABLE_SCHEMA + CHAR(7) + C.TABLE_NAME)) 
                 % @PARALLELIZE =  @DIVIDE);

-- loop over the rows of the searching table

DECLARE C CURSOR
FOR SELECT GRP_ID, GRP_QUERY   
    FROM   #T_P_GREP;

DECLARE @GRP_ID INT, @GRP_QUERY NVARCHAR(1200);

OPEN C;

FETCH C  INTO @GRP_ID, @GRP_QUERY;

-- each row is a distinct search for a column in the table

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = N'INSERT INTO #T_P_GREP_TEST SELECT 1 WHERE EXISTS(' 
            + @GRP_QUERY + ');'
   EXEC (@SQL)
   UPDATE #T_P_GREP
   SET GRP_FOUND = CASE @@ROWCOUNT WHEN 0 THEN 0 ELSE 1 END,
       GRP_SEARCH = 1
   WHERE GRP_ID = @GRP_ID;
   FETCH C  INTO @GRP_ID, @GRP_QUERY;
END
CLOSE C;
DEALLOCATE C;

-- viewing the queries that finds almost one searched value

SELECT @DATA AS RECHERCHE, 
       CAST(@DIVIDE + 1 AS VARCHAR(16)) + ' / ' + CAST(@PARALLELIZE AS VARCHAR(16)) 
          AS GRP_PARTIAL,  
       GRP_SCHEMA, GRP_TABLE, GRP_COLUMN, GRP_QUERY 
FROM   #T_P_GREP 
WHERE  GRP_FOUND = 1;

DROP TABLE #T_P_GREP_TEST;
GO