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

[riahc3]

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!

[smantscheff]

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.

[riahc3]

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]

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.

[adam_k]

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    
   -- http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
   -- 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

[riahc3]

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 -- http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm -- 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]

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.