Sql как найти значение во всех таблицах

If you are avoiding stored procedures like the plague, or are unable to do a mysql_dump due to permissions, or running into other various reasons.

I would suggest a three-step approach like this:

1) Where this query builds a bunch of queries as a result set.

# =================
# VAR/CHAR SEARCH
# =================
# BE ADVISED USE ANY OF THESE WITH CAUTION
# DON'T RUN ON YOUR PRODUCTION SERVER 
# ** USE AN ALTERNATE BACKUP **

SELECT 
    CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE ', A.COLUMN_NAME, ' LIKE '%stuff%';') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     
        (
            A.DATA_TYPE LIKE '%text%'
        OR  
            A.DATA_TYPE LIKE '%char%'
        )
;

.

# =================
# NUMBER SEARCH
# =================
# BE ADVISED USE WITH CAUTION

SELECT 
    CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE ', A.COLUMN_NAME, ' IN ('%1234567890%');') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     A.DATA_TYPE IN ('bigint','int','smallint','tinyint','decimal','double')
;

.

# =================
# BLOB SEARCH
# =================
# BE ADVISED THIS IS CAN END HORRIFICALLY IF YOU DONT KNOW WHAT YOU ARE DOING
# YOU SHOULD KNOW IF YOU HAVE FULL TEXT INDEX ON OR NOT
# MISUSE AND YOU COULD CRASH A LARGE SERVER
SELECT 
    CONCAT('SELECT CONVERT(',A.COLUMN_NAME, ' USING utf8) FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE CONVERT(',A.COLUMN_NAME, ' USING utf8) IN ('%someText%');') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     A.DATA_TYPE LIKE '%blob%'
;

Results should look like this:

2) You can then just Right Click and use the Copy Row (tab-separated)

3) Paste results in a new query window and run to your heart’s content.

Detail: I exclude system schema’s that you may not usually see in your workbench unless you have the option Show Metadata and Internal Schemas checked.

I did this to provide a quick way to ANALYZE an entire HOST or DB if needed or to run OPTIMIZE statements to support performance improvements.

I’m sure there are different ways you may go about doing this but here’s what works for me:

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO ANALYZE THEM
SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO OPTIMIZE THEM
SELECT CONCAT('OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

Tested On MySQL Version: 5.6.23

WARNING: DO NOT RUN THIS IF:

1. You are concerned with causing Table-locks (keep an eye on your client-connections)

2. You are unsure about what you are doing.

3. You are trying to anger you DBA. (you may have people at your desk with the quickness.)

Cheers, Jay ;-]

Время на прочтение
5 мин

Количество просмотров 62K

Описание общей потребности в поиске данных и объектов в базе данных

Поиск данных, а также хранимых процедур, таблиц и других объектов в базе данных является достаточно актуальным вопросом в том числе и для C#-разработчиков, а также и для .NET-разработки в целом.

Достаточно часто может возникнуть ситуация, при которой нужно найти:

1. объект базы данных (таблицу, представление, хранимую процедуру, функцию и т д)
2. данные (значение и в какой таблице располагается)
3. фрагмент кода в определениях объектов базы данных

Существует множество готовых решений как платных, так и бесплатных.

Сначала рассмотрим как можно осуществлять поиск данных и объектов в базе данных с помощью встроенных средств самой СУБД, а затем рассмотрим как это сделать с помощью бесплатной утилиты dbForge Search.

Поиск с помощью встроенных средств самой СУБД

Определить есть ли таблица Employee в базе данных можно с помощью следующего скрипта:

select [object_id], [schema_id],
	   schema_name([schema_id]) as [schema_name], 
	   [name], 
	   [type], 
	   [type_desc], 
	   [create_date], 
	   [modify_date]
from sys.all_objects
where [name]='Employee';

Результат может быть примерно такой:

Здесь выводятся:

1. идентификаторы объекта и схемы, где располагается объект
2. название этой схемы и название этого объекта
3. тип объекта и описание этого типа объекта
4. даты и время создания и последней модификации объекта

Чтобы найти все вхождения строки “Project”, то можно использовать следующий скрипт:

select [object_id], [schema_id],
	   schema_name([schema_id]) as [schema_name], 
	   [name], 
	   [type], 
	   [type_desc], 
	   [create_date], 
	   [modify_date]
from sys.all_objects
where [name] like '%Project%';

Результат может быть примерно такой:

Как видно из результата, здесь подстроку “Project” содержат не только две таблицы Project и ProjectSkill, но и также некоторые первичные и внешние ключи.

Чтобы понять кому именно принадлежат данные ключи, добавим в вывод поле parent_object_id и его имя и схему, в которой он располагается следующим образом:

select ao.[object_id], ao.[schema_id],
	   schema_name(ao.[schema_id]) as [schema_name],
	   ao.parent_object_id,
	   p.[schema_id] as [parent_schema_id],
	   schema_name(p.[schema_id]) as [parent_schema_name],
	   p.[name] as [parent_name],
	   ao.[name], 
	   ao.[type], 
	   ao.[type_desc], 
	   ao.[create_date], 
	   ao.[modify_date]
from sys.all_objects as ao
left outer join sys.all_objects as p on ao.[parent_object_id]=p.[object_id]
where ao.[name] like '%Project%';

Результатом будет вывод таблицы с детальной информацией о родительских объектах, т е где определены первичные и внешние ключи:

В запросах используются следующие системные объекты:

• таблица sys.all_objects
• скалярная функция schema_name

Итак, разобрали как найти объекты в базе данных с помощью встроенных средств самой СУБД.
Теперь покажем как найти данные в базе данных на примере поиска строк.

Чтобы найти строковое значение по всем таблицам базы данных, можно воспользоваться следующим решением. Упростим данное решение и покажем как можно найти например значение “Ramiro” с помощью следующего скрипта:

set nocount on
declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
set @substr = '%Ramiro%'

declare @sql nvarchar(max);

create table #rslt 
(table_name varchar(128), field_name varchar(128), [value] nvarchar(max))

declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
open s
fetch next from s into @name
while @@fetch_status = 0
begin
 declare c cursor for 
	select quotename(column_name) as column_name from information_schema.columns 
	  where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname', 'int', 'tinyint') and table_name  = @name
 set @name = quotename(@name)
 open c
 fetch next from c into @column
 while @@fetch_status = 0
 begin
   --print 'Processing table - ' + @name + ', column - ' + @column

   set @sql='insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', cast(' + @column + 
	' as nvarchar(max)) from' + @name + ' where cast(' + @column + ' as nvarchar(max)) like ''' + @substr + '''';

	print @sql;

   exec(@sql);

   fetch next from c into @column;
 end
 close c
 deallocate c
 fetch next from s into @name
end
select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
group by table_name, field_name
order by table_name, field_name

drop table #rslt
close s
deallocate s

Результат выполнения может быть таким:

Здесь выводятся имена таблиц и в каких столбцах хранится значение, содержащие подстроку “Ramiro”. А также количество найденных входов данной подстроки для найденной пары таблица-колонка.

Чтобы найти объекты, в определениях которых есть заданный фрагмент кода, можно воспользоваться следующими системными представлениями:

1. sys.sql_modules
2. sys.all_sql_modules
3. sys.syscomments

Например, используя последнее представление, можно с помощью следующего скрипта найти все объекты, в определениях которых встречается заданный фрагмент кода:

select obj.[object_id],
	   obj.[name],
	   obj.[type_desc],
	   sc.[text]
from sys.syscomments as sc
inner join sys.objects obj on sc.[id]=obj.[object_id]
where sc.[text] like '%code snippet%';

Здесь будет выведен идентификатор, название, описание и полное определение объекта.

Поиск с помощью бесплатной утилиты dbForge Search

Однако, более удобно поиск производить с помощью готовых хороших инструментов. Одним из таких инструментов является dbForge Search.

Для вызова этой утилиты в окне SSMS нажмите на кнопку .

Появится следующее окно поиска:

Обратите внимание на верхнюю панель (слева направо):

1. можно переключать режим поиска (ищем DDL (объекты) или данные)
2. непосредственно что ищем (какую подстроку)
3. учитывать ли регистр, искать точное соответствие слову, искать вхождения:

   

4. группировать результат по типам объектов — кнопка
5. выбрать нужные типы объектов для поиска:

   

6. также можно задать несколько баз данных для поиска и выбрать экземпляр MS SQL Server

Это все в режиме поиска объектов, т е когда включен DDL:

В режиме поиска данных изменится только выбор типов объектов:

А именно будут доступны для выбора только таблицы, где и хранятся собственно сами данные:

Теперь как и раньше найдем все вхождения подстроки “Project” в названиях объектов:

Как видно, был выбран режим поиска по DDL-объектам, заполнено что ищем-строка “Project”, остальное все было по умолчанию.

При выделении найденного объекта внизу отображается код определения данного объекта или всего его родительского объекта.

Также можно переместить навигацию на найденный объект, щелкнув на кнопку :

Можно также сгруппировать найденные объекты по их типу:

Обратите внимание, что выводятся даже те таблицы, в которых есть поля, в именах которых содержится подстрока “Project”. Однако, напомним, что режим поиска можно менять: искать полное соответствие/частичное/учитывать регистр или нет.

Теперь найдем значение “Ramiro” по всем таблицам:

Обратите внимание, что внизу отображаются все строки, в которых содержится подстрока “Ramiro” выбранной таблицы Employee.

Также можно переместить навигацию к найденному объекту, нажав как и ранее на кнопку :

Таким образом мы можем искать нужные объекты и данные в базе данных.

Заключение

Были рассмотрены способы поиска как самих данных, так и объектов в базе данных как с помощью встроенных средств самой СУБД MS SQL Server, так и с помощью бесплатной утилиты dbForge Search.

Также от компании Devart есть и ряд других бесплатных готовых решений, полный список которых можно посмотреть здесь.

Источники

• Search_Script.sql
• SSMS
• dbForge Search
• Документация по Microsoft SQL
• Бесплатные решения от компании Devart

Or, you can use my query here, should be simpler then having to create sProcs for each DB you want to search: FullParam SQL Blog

/* Reto Egeter, fullparam.wordpress.com */

DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

WHILE @TableName IS NOT NULL
BEGIN
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
    )
    IF @TableName IS NOT NULL
    BEGIN
        DECLARE @sql VARCHAR(MAX)
        SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE       TABLE_SCHEMA    = PARSENAME(''' + @TableName + ''', 2)
                AND TABLE_NAME  = PARSENAME(''' + @TableName + ''', 1)
                AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
                AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'
        INSERT INTO @ColumnNameTable
        EXEC (@sql)
        WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
        BEGIN
            PRINT @ColumnName
            SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
            SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' 
            WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
            ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
                    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
                    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
            INSERT INTO #Results
            EXEC(@sql)
            IF @@ROWCOUNT > 0 IF @FullRowResult = 1 
            BEGIN
                SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
                    ' FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
                    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
                    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
                EXEC(@sql)
            END
            DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
        END 
    END
END
SET NOCOUNT OFF

SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType

Есть несколько способов произвести поиск по БД, когда мы не понимаем где нам нужно искать:

1. Подумать как могут называться нужные нам таблицы и произвести поиск по названиям таблиц, после чего поискать там
2. Поиска можно не только по таблицам, но и по колонкам
3. Два предыдущих способа не дают 100% результата, поэтому предлагаю вам рассмотреть ещё один. Этот способ даёт больше шансов найти то что мы ищем, но он значительно дольше и тратит ресурсы нашего сервера.
4. Можно воспользоваться плагином для SSMS — ApexSQL Search

Рассмотрим 3-ий способ подробнее. Суть заключается в том, чтобы произвести поиск по всем таблицам и колонкам в Базе Данных. Чтобы скриптом было удобно пользоваться, мы обернём его в процедуру SearchAllTables, которая ищет по всем колонкам типа char, varchar, nchar, nvarchar по всей БД (системные таблицы исключены из поиска)

Вывод этой процедуры состоит из 2х колонок:

1. Имя таблица и колонки, в которой были найдены совпадения
2. Текст, в котором были найдены совпадения (будут отображены только первые 3630 символов)

Будьте осторожны, на больших Базах Данных скрипт может работать очень долго (часы). Так что запуская его на больших БД, будьте готовы ждать.

Создайте процедуру в той БД, в которой необходимо произвести поиск:

Вызов процедуры выглядит так:

Так же есть очень похожий вариант этого скрипта через другие представления. На этот раз мы обойдёмся без процедур. Строку поиска необходимо указать в @SearchText :

Интересная, но более сложная, реализация поиска (проверено только на SQL Server 2014)

Вырезка из статей:

1. How to search all columns of all tables in a database for a keyword?
2. How to quickly search for SQL database data and objects

Запись опубликована в рубрике Полезно и интересно с метками t-sql. Добавьте в закладки постоянную ссылку.

Автор: RXL.
Дата написания: 3.12.2009.
Права на статью принадлежат автору и Клубу программистов «Весельчак У».

• Вводная.
• Информация о таблицах и столбцах.
• Генерация SQL-кода.
• Автоматизация выполнения.
• Вдогонку.

Во время администрирования баз данных с большим количеством таблиц иногда возникает потребность найти, в какой таблице хранится определенная строка. Расскажу об этом вкратце, но с живыми примерами.

MySQL предоставляет информацию об объектах баз данных через специальную схему «INFORMATION_SCHEMA». Например, так выглядит состав таблиц этой схемы в MySQL 5.0:

mysql> SHOW TABLES FROM information_schema;
+—————————————+
| Tables_in_information_schema          |
+—————————————+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+—————————————+
17 rows in set (0.00 sec)

Информация о колонках:

mysql> DESC information_schema.columns;
+—————————+—————+——+——+———+——-+
| Field                    | Type         | Null | Key | Default | Extra |
+—————————+—————+——+——+———+——-+
| TABLE_CATALOG            | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA             | varchar(64)  | NO   |     |         |       |
| TABLE_NAME               | varchar(64)  | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)  | NO   |     |         |       |
| ORDINAL_POSITION         | bigint(21)   | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext     | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)   | NO   |     |         |       |
| DATA_TYPE                | varchar(64)  | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21)   | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21)   | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21)   | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21)   | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(64)  | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(64)  | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext     | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)   | NO   |     |         |       |
| EXTRA                    | varchar(20)  | NO   |     |         |       |
| PRIVILEGES               | varchar(80)  | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(255) | NO   |     |         |       |
+—————————+—————+——+——+———+——-+
19 rows in set (0.00 sec)

Зачем я это рассказываю? Затем, что если вы будете писать руками SQL-запрос для каждой таблицы в схеме да еще и для каждого подходящего столбца — можно смело менять профессию, т.к. это точно не ваше. Нормальный человек найдет обобщенное решение, без тупого и бессмысленного труда. Это решение — создать необходимые запросы автоматически, и поможет нам в этом информация о таблицах и столбцах из таблицы «COLUMNS».

Нам нужно отобрать таблицы, в которых есть колонки подходящих для нас типов: CHAR, VARCHAR, TEXT и т.п. Всего 12 типов. Фильтровать будем по полю «DATA_TYPE». Такой запрос наглядно показывает типы столбцов в таблицах схемы «test»:

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = ‘test’
  AND data_type IN (
    ‘char’, ‘varchar’, ‘binary’, ‘varbinary’,
    ‘tinytext’, ‘text’, ‘mediumtext’, ‘longtext’,
    ‘tinyblob’, ‘blob’, ‘mediumblob’, ‘longblob’
  );

Если не на чем экспериментировать, воспользуйтесь этой таблицей:

CREATE TABLE `t2` (
  `f_vch` varchar(10) default NULL,
  `f_bin` varbinary(10) default NULL,
  `f_tblob` tinyblob,
  `f_blob` blob,
  `f_ttext` tinytext,
  `f_text` text,
  `f_ltext` longtext
);

Составим SQL-запрос, который создаст нам набор SQL-запросов для каждой таблицы указанной схемы.

SELECT CONCAT(
    ‘SELECT «‘, c.table_name, ‘» `$table$`’,
    ‘ FROM `’, c.table_schema, ‘`.`’, c.table_name, ‘`’
    ‘ WHERE ‘, GROUP_CONCAT(
      CONCAT(‘`’, c.column_name, ‘`’)
      SEPARATOR ‘ LIKE «%test%» OR ‘
    ), ‘ LIKE «%test%«‘,
    ‘ LIMIT 1’
  ) query
FROM information_schema.columns c
WHERE c.table_schema = ‘test’
  AND c.data_type IN (
    ‘char’, ‘varchar’, ‘binary’, ‘varbinary’,
    ‘tinytext’, ‘text’, ‘mediumtext’, ‘longtext’,
    ‘tinyblob’, ‘blob’, ‘mediumblob’, ‘longblob’
  )
GROUP BY c.table_name;

Например, у меня запрос вернул только одну строку для схемы «test»:

SELECT «t2» `$table$`
 FROM `test`.`t2`
 WHERE `f_vch` LIKE «%test%«
  OR `f_bin` LIKE «%test%«
  OR `f_tblob` LIKE «%test%«
  OR `f_blob` LIKE «%test%«
  OR `f_ttext` LIKE «%test%«
  OR `f_text` LIKE «%test%«
  OR `f_ltext` LIKE «%test%«
  OR `f_vbin` LIKE «%test%«
 LIMIT 1

Полученный запрос будет искать во всех текстовых полях вхождение строки «test» и остановится, найдя первую подходящую строку. Возможно, в него стоит добавить ORDER BY NULL для оптимизации под большие таблицы. Выводить саму строку я не стал — она может быть большой и неудобоваримой для просмотра в консоли. Тут уже дело вкуса — переделайте запрос, как вам захочется. Я вот переделал так:

SELECT CONCAT(
    ‘SELECT «‘, c.table_name, ‘» `$table$`, ‘, GROUP_CONCAT(
      CONCAT(‘SUM(IF(`’, c.column_name, ‘` LIKE «%test%«, 1, 0)) `’, c.column_name, ‘`’)
      SEPARATOR ‘, ‘
    ),
    ‘ FROM `’, c.table_schema, ‘`.`’, c.table_name, ‘`’
    ‘ WHERE ‘, GROUP_CONCAT(
      CONCAT(‘`’, c.column_name, ‘` LIKE «%test%«‘)
      SEPARATOR ‘ OR ‘
    )
  ) query
FROM information_schema.columns c
WHERE c.table_schema = ‘test’
  AND c.data_type IN (
    ‘char’, ‘varchar’, ‘binary’, ‘varbinary’,
    ‘tinytext’, ‘text’, ‘mediumtext’, ‘longtext’,
    ‘tinyblob’, ‘blob’, ‘mediumblob’, ‘longblob’
  )
GROUP BY c.table_name;

Генерируемые запросы будут выводить по одной строке, где первая колонка содержит имя таблицы, а все последующие колонки будут содержать количество совпавших строк для каждого проверяемого поля таблицы. Выглядит это так:

mysql> SELECT «t2» `$table$`,
  SUM(IF(`f_vch` LIKE «%test%», 1, 0)) `f_vch`,
  SUM(IF(`f_bin` LIKE «%test%», 1, 0)) `f_bin`,
  SUM(IF(`f_tblob` LIKE «%test%», 1, 0)) `f_tblob`,
  SUM(IF(`f_blob` LIKE «%test%», 1, 0)) `f_blob`,
  SUM(IF(`f_ttext` LIKE «%test%», 1, 0)) `f_ttext`,
  SUM(IF(`f_text` LIKE «%test%», 1, 0)) `f_text`,
  SUM(IF(`f_ltext` LIKE «%test%», 1, 0)) `f_ltext`,
  SUM(IF(`f_vbin` LIKE «%test%», 1, 0)) `f_vbin`
 FROM `test`.`t2`
 WHERE `f_vch` LIKE «%test%»
  OR `f_bin` LIKE «%test%»
  OR `f_tblob` LIKE «%test%»
  OR `f_blob` LIKE «%test%»
  OR `f_ttext` LIKE «%test%»
  OR `f_text` LIKE «%test%»
  OR `f_ltext` LIKE «%test%»
  OR `f_vbin` LIKE «%test%»;
+———+——-+——-+———+———+———+———+———+———+
| $table$ | f_vch | f_bin | f_tblob | f_blob | f_ttext | f_text | f_ltext | f_vbin |
+———+——-+——-+———+———+———+———+———+———+
| t2      |     1 |     0 |       0 |      0 |       0 |      0 |       0 |      0 |
+———+——-+——-+———+———+———+———+———+———+
1 row in set (0.00 sec)

Теперь напишем процедуру, которая сделает все автоматически: создаст запросы, подставит в них искомый текст и выдаст нам статистику по вхождению искомой строки. Исполнять генерируемые запросы будем посредством prepared statements.

Заметьте, что искомый текст я вставлял непосредственно в запрос. Это ограничение агрегатной функции GROUP_CONCAT — она допускает только константную строку в качестве разделителя — переменную туда подставить не удастся. (Данное ограничение позже решилось выносом подстановки искомой строки из SEPARATOR в группируемое выражение.)

Для решения этой проблемы в процедуре я делаю подстановку функцией REPLACE в уже готовом запросе. Искомую строку стоит предварительно обработать для экранирования недопустимых символов, чтобы не было неприятных сбоев. Здесь я сделал лишь замену одинарной кавычки на обратный слеш и кавычку, но правильнее будет обработать искомую строку функцией QUOTE и удалить из результата первый и последний символ (это тоже одинарные кавычки — их подставляет QUOTE).

Готовый и протестированный код процедуры:

Смотрите, разбирайтесь. Потенциально данный код не защищен от SQL-injection, но и назначение у него чисто административное.

Вопросы можно задать на нашем форуме. Статья писалась быстро и, возможно что-то, стоит переделать или добавить — буду раз замечаниям и предложениям.

Вдогонку к уже выпущенной статье: в приведенной процедуре есть неприятное ограничение, накладываемое функцией GROUP_CONCAT на длину производимой ею строки. Переписал процедуру и исправил некоторые недочеты.

Быстродействие проверено на схеме с 62-я таблицам MYISAM общим объемом 148 МБ (только файлы MYD), на сервере с двумя одноядерными Xeon 2.8 ГГц и 3 ГБ DDR1 ECC.

Query OK, 0 rows affected (3.07 sec)

Повторный вызов отрабатывает за 2.58 секунды.

Следующую модификацию процедуры find_overall не рекомендую запускать в консоли. Он вернет множество рекордсетов — по одному на каждую проверяемую таблицу.