How do I search for a text in a database but I dont know what table its in

Question

riahc3 50 Â

14 Years Ago

Hey

I want to search for a text string but I dont know what table or column it is in. I just know the database that it is in. I know also that the type is a text type.

Any query I can do or in MySQL Workbench?

Thanks!

mysql

3 Contributors
6 Replies
448 Views
6 Days Discussion Span
Latest Post 14 Years Ago Latest Post by smantscheff

Dani AI

Generated 9 Months Ago

Quick summary and pragmatic choices for : if this is a one‑time hunt across ~200–300 tables, use an offline text search (fast and simple). If you need to repeat the search, use a script or stored routine that walks INFORMATION_SCHEMA and runs a LIKE check on every text column. ’s dump+grep idea is the fastest for a single run; ’s MS‑SQL procedure is the right pattern — below is a MySQL version and some practical notes.

One‑off (fast, no SQL coding required)

# dump data-only and grep (runs locally, read-only)
mysqldump -u user -p --no-create-info mydb | grep -in 'search text'

Stored routine (run inside MySQL; returns matches and small snippets)

DELIMITER $$
CREATE PROCEDURE search_all_text(IN in_db VARCHAR(64), IN in_search VARCHAR(255))
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tname VARCHAR(64);
  DECLARE cname VARCHAR(64);
  DECLARE cur CURSOR FOR
    SELECT TABLE_NAME, COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = in_db
      AND DATA_TYPE IN ('char','varchar','tinytext','text','mediumtext','longtext');
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_search_results (location VARCHAR(255), snippet TEXT);

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO tname, cname;
    IF done THEN LEAVE read_loop; END IF;
    SET @loc = QUOTE(CONCAT(tname, '.', cname));
    SET @pattern = QUOTE(CONCAT('%', in_search, '%'));
    SET @sql = CONCAT(
      'INSERT INTO tmp_search_results(location, snippet) ',
      'SELECT ', @loc, ', LEFT(`', cname, '`, 200) FROM `', in_db, '`.`', tname, '` WHERE `', cname, '` LIKE ', @pattern
    );
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;

  SELECT * FROM tmp_search_results;
END$$
DELIMITER ;

-- call example:
-- CALL search_all_text('mydb', 'needle');

Notes and cautions: this will scan every text column and can be slow on large DBs—run on a copy or a replica if possible. LIKE respects column collations (case sensitivity depends on collation); add COLLATE or use LOWER() if needed. For repeated searches, consider adding FULLTEXT indexes or using an external search engine (Elasticsearch/Sphinx) for better performance.

All 6 Replies

smantscheff 265 Veteran Poster

14 Years Ago

You could use the concat function:

select * from mytable where concat(field1,field2,field3...) like '%searchtext%'

This is highly inefficient, but with small databases it works.
An alternative is to export (dump) the database to a text file and then search with grep or a text editor.

adam_k 239 Master Poster

14 Years Ago

In my library I've got a script found ages ago in the internet. It's for MS SQL server but it might help you get an idea or something.

This stored procedure creates a temporary table for storing search result data and steps through each column found.  
CREATE PROC [dbo].[SearchAllTablesAllColumns]
(
   @SearchStr nvarchar(100)
)
AS
BEGIN
   -- Purpose: To search all columns in all tables for a given search string
   -- Written by: Francisco Tapia
   -- Site: http://sqlthis.blogspot.com
   -- Inspired by: Narayana Vyas Kondreddi    
   -- 
   -- Tested on: SQL Server 7.0, 2000 and 2005
   -- Date modified: June 23, 2009

   CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

   SET NOCOUNT ON

   DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT

   SELECT @TableName = '', @ColumnName = ''
   SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''')

      WHILE (@ColumnName IS NOT NULL) 
      BEGIN
      SET @ColumnName = (
         SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name)
         + '|' + QUOTENAME(C.Column_name))
               FROM INFORMATION_SCHEMA.COLUMNS c
                  INNER JOIN INFORMATION_SCHEMA.TABLES t on c.table_schema = t.table_schema and c.Table_name = t.table_name
               WHERE    T.TABLE_TYPE = 'BASE TABLE'
                  AND C.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')
                  AND   QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '.' + QUOTENAME(COLUMN_NAME) > @TableName 

+ '.' + @ColumnName 

               )

         SET @Parse = PATINDEX ('%|%', @ColumnName)

         SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1)
         SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1, LEN(@ColumnName))    

         IF @ColumnName IS NOT NULL
         BEGIN
            INSERT INTO #Results
            EXEC
            (
               'SELECT ''' + @TableName + '.' + @ColumnName + ''', SUBSTRING('
               + @ColumnName + ',1, 3630) 
               FROM ' + @TableName + ' (NOLOCK) ' +
               ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
            )
         END
      END   

   SELECT ColumnName, ColumnValue FROM #Results
   ORDER BY ColumnName 

END

pritaeas commented: Interesting +13

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

riahc3 50 Â Team Colleague · Answer 1 · 2012-04-17T10:19:19+00:00

You could use the concat function:

select * from mytable where concat(field1,field2,field3...) like '%searchtext%'
This is highly inefficient, but with small databases it works.
An alternative is to export (dump) the database to a text file and then search with grep or a text editor.

The problem is that there is a large ammount of columns so I can add them. Does
:

Select * from * where concat(*) like 'textimsearchingforhere'

Work?

smantscheff 265 Veteran Poster · Answer 2 · 2012-04-17T12:57:50+00:00

What kind of a problem is that? Is the number of columns larger than the maximum number of arguments for concat? Or are you just too lazy to type all the names?
No, your suggestion does not work. concat takes string arguments.

riahc3 50 Â Team Colleague · Answer 3 · 2012-04-23T16:14:15+00:00

What kind of a problem is that? Is the number of columns larger than the maximum number of arguments for concat? Or are you just too lazy to type all the names?
No, your suggestion does not work. concat takes string arguments.

Well, there are about 200-300 tables. Im not sure if that reaches the max and/or you would type them ALL out.....

In my library I've got a script found ages ago in the internet. It's for MS SQL server but it might help you get an idea or something. This stored procedure creates a temporary table for storing search result data and steps through each column found. CREATE PROC [dbo].[SearchAllTablesAllColumns]( @SearchStr nvarchar(100))ASBEGIN -- Purpose: To search all columns in all tables for a given search string -- Written by: Francisco Tapia -- Site: http://sqlthis.blogspot.com -- Inspired by: Narayana Vyas Kondreddi -- -- Tested on: SQL Server 7.0, 2000 and 2005 -- Date modified: June 23, 2009 CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT SELECT @TableName = '', @ColumnName = '' SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''') WHILE (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '|' + QUOTENAME(C.Column_name)) FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t on c.table_schema = t.table_schema and c.Table_name = t.table_name WHERE T.TABLE_TYPE = 'BASE TABLE' AND C.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext') AND QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '.' + QUOTENAME(COLUMN_NAME) > @TableName + '.' + @ColumnName ) SET @Parse = PATINDEX ('%|%', @ColumnName) SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1) SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1, LEN(@ColumnName)) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', SUBSTRING(' + @ColumnName + ',1, 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr ) END END SELECT ColumnName, ColumnValue FROM #Results ORDER BY ColumnName END

Intresting :) Thanks

smantscheff 265 Veteran Poster · Answer 4 · 2012-04-23T22:19:32+00:00

Well, there are about 200-300 tables. Im not sure if that reaches the max and/or you would type them ALL out.....

If this is a one-time problem, dump the database and grep the output.
If it is a recurring challenge, use a script like adam_k proposed. In PHP you could write a routine which retrieves all table names, then walks through the tables retrieving all the column names and combining them into a concat expression.

How do I search for a text in a database but I dont know what table its in

Dani AI

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers