text: IF Object_id('dbo.usp_SearchOnAllDB', 'P') IS NULL
    EXECUTE ('CREATE PROCEDURE dbo.usp_SearchOnAllDB AS SELECT 1');
  GO
  
  ALTER PROCEDURE dbo.usp_SearchOnAllDB(@phrase         VARCHAR(8000),
                                       @OutFullRecords BIT = 0)
  AS
      /*
         To apply so:
            exec  sp_SearchOnAllDB 'Arme%'
            exec  sp_SearchOnAllDB '%soft%'
            exec  sp_SearchOnAllDB '_5234_57%', 1
            exec  sp_SearchOnAllDB M_cro_oft
      */
      DECLARE @sql NVARCHAR(max);
      DECLARE @schema SYSNAME;
      DECLARE @tbl SYSNAME;
      DECLARE @col SYSNAME;
      DECLARE @id_present BIT
      DECLARE @is_char_phrase BIT
      DECLARE @min_len INT
      DECLARE @loop_idx INT
      DECLARE @loop_chr CHAR(1)
  
      SET nocount ON
  
      IF Isnull(@phrase, '') = ''
        BEGIN
            RAISERROR('Phrase is absent',16,-1)
  
            RETURN
        END
  
      -- Handle Quotes passed in the search string
      SET @phrase = Replace(@phrase, '''', '''''')
  
      SELECT @loop_idx = 1,
             @is_char_phrase = 0,
             @min_len = 0
  
      WHILE @loop_idx <= Len(@phrase)
        BEGIN
            SET @loop_chr = Substring(@phrase, @loop_idx, 1)
  
            IF @loop_chr NOT IN ( '%', '_' )
              SET @min_len = @min_len + 1
  
            IF @is_char_phrase = 0
               AND @loop_chr NOT IN ( '%', '_', '0', '1',
                                      '2', '3', '4', '5',
                                      '6', '7', '8', '9', '.' )
              SET @is_char_phrase = 1
  
            SET @loop_idx = @loop_idx + 1
        END
  
      CREATE TABLE #tbl_res
        (
           SchemaName  SYSNAME NOT NULL,
           TableName   SYSNAME NOT NULL,
           ColumnName  SYSNAME NOT NULL,
           Id          INT NULL,
           ColumnValue NVARCHAR(max) NOT NULL
        );
  
      CREATE TABLE #tbl_res2
        (
           SchemaName  SYSNAME NOT NULL,
           TableName   SYSNAME NOT NULL,
           ColumnName  SYSNAME NOT NULL,
           Id          INT NULL,
           ColumnValue NVARCHAR(max) NOT NULL
        );
  
      DECLARE crr CURSOR local fast_forward FOR
        SELECT s.NAME AS SchemaName,
               t.NAME AS TableName,
               c.NAME AS ColumnName,
               1      AS id_present
        FROM   sys.objects t
               INNER JOIN sys.syscolumns c
                       ON c.id = t.object_id
               INNER JOIN sys.schemas AS s
                       ON t.schema_id = s.schema_id
        WHERE  t.type = 'U'
               AND c.status&0x80 = 0 -- Not IDENTITY
               AND EXISTS (SELECT *
                           FROM   syscolumns c2
                           WHERE  t.object_id = c2.id
                                  AND c2.status&0x80 = 0x80
                                  AND c2.xtype IN ( 48, 52, 56 ))
               AND ( ( @is_char_phrase = 1
                       AND c.xtype IN ( 175, 239, 99, 231,
                                        35, 167 )
                       AND c.length >= @min_len ) -- char only
                      OR ( @is_char_phrase = 0
                           AND c.xtype NOT IN ( 34, 165, 173, 189,
                                                61, 58, 36 ) ) )
        -- char and numeric
        UNION
        SELECT s.name,
               t.name,
               c.name,
               0
        FROM   sys.objects t
               INNER JOIN sys.syscolumns c
                       ON c.id = t.object_id
               INNER JOIN sys.schemas AS s
                       ON t.schema_id = s.schema_id
        WHERE  t.type = 'U'
               AND NOT EXISTS (SELECT *
                               FROM   syscolumns c2
                               WHERE  t.object_id = c2.id
                                      AND c2.status&0x80 = 0x80
                                      AND c2.xtype IN ( 48, 52, 56 ))
               AND ( ( @is_char_phrase = 1
                       AND c.xtype IN ( 175, 239, 99, 231,
                                        35, 167 )
                       AND c.length >= @min_len ) -- char only
                      OR ( @is_char_phrase = 0
                           AND c.xtype NOT IN ( 34, 165, 173, 189,
                                                61, 58, 36 ) ) )
        -- char and numeric
        ORDER  BY 1,
                  2;
  
      OPEN crr
      FETCH crr INTO @schema, @tbl, @col, @id_present
      WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @OutFullRecords = 0
              BEGIN
                  SET @sql =
      'insert into #tbl_res (SchemaName, TableName, ColumnName, Id, ColumnValue) '
      + 'select ''[' + @schema + ']'', ''[' + @tbl
      + ']'', ''[' + @col + ']'', '
  
      IF @id_present = 1
        SET @sql = @sql + 'IDENTITYCOL, '
      ELSE
        SET @sql = @sql + 'NULL, '
  
      SET @sql = @sql + 'convert(NVARCHAR(MAX), [' + @col + ']) '
                 + 'from [' + @schema + '].[' + @tbl + '] (nolock) '
                 + 'where convert(varchar(8000), [' + @col
                 + ']) like ''' + @phrase + ''' '
      END
  
      IF @OutFullRecords = 1
        BEGIN
            SET @sql = 'if exists (select * from [' + @tbl
                       + '] (nolock) '
                       + 'where convert(varchar(8000), [' + @col
                       + ']) like ''' + @phrase + ''') ' + 'select ''['
                       + @tbl + ']'' TableName, ''[' + @col
                       + ']'' ColumnName, * ' + 'from [' + @tbl
                       + '] (nolock) where convert(varchar(8000), ['
                       + @col + ']) like ''' + @phrase + ''';'
        END
      EXEC(@sql);
  
      FETCH crr INTO @schema, @tbl, @col, @id_present;
      END
  
      CLOSE crr;
      DEALLOCATE crr;
  
      IF @OutFullRecords = 0
        BEGIN
            INSERT #tbl_res2
            SELECT SchemaName,
                   TableName,
                   ColumnName,
                   Id,
                   CONVERT(VARCHAR(255), ColumnValue) ColumnValue
            FROM   #tbl_res;
        END
      SELECT SchemaName,
             TableName,
             ColumnName,
             ColumnValue
      FROM   #tbl_res2
      GROUP  BY SchemaName,
                TableName,
                ColumnName,
                ColumnValue
      ORDER  BY SchemaName,
                TableName;
  GO 