Поиск фрагмента текста по всей базе данных
Проблема
Нужно найти строки, содержащие заданный фрагмент текста, в любых столбцах любых таблиц базы данных.
Мне неоднократно встречался такой вопрос в профессиональных социальных сетях. Предлагаемое решение написано для SQL Server, хотя его без особого труда можно адаптировать и для других СУБД/диалектов.
Решение
Алгоритм
- Для каждой таблицы конкатенируем текстовые столбцы с некоторым разделителем, которого заведомо не может быть в поисковой строке. Это позволит нам избежать ситуации, когда искомый фрагмент текста может начинаться в одном столбце строки таблицы, а заканчиваться - в другом.
- Для каждой таблицы формируем строку запроса для динамического выполнения.
- Полученный в результате первых двух пунктов запрос будем использовать в качестве источника строк для курсора.
- При обходе курсора будем динамически выполнять построенный оператор. Чтобы избежать вывода всех таблиц, дополним скрипт критерием существования искомых строк в результатах поиска. Тогда на выходе будут только те строки/таблицы, в которых содержится искомая подстрока.
Пункт 1 алгоритма
Столбцы таблиц можно извлечь из системного представления INFORMATION_SCHEMA.COLUMNS. Поскольку у нас появилась замечательная функция CONCAT, то нет особой необходимости выбирать только столбцы строковых типов данных. Дело в том, что функция CONCAT неявно преобразовывает данные к строковому типу и, кроме того, заменяет при этом NULL-значение пустой строкой.
Замечания. Не все типы данных могут быть неявно преобразованы к строке. Устаревший тип IMAGE нельзя преобразовать, поэтому в коде исключается таблица dtproperties, содержащая данные для построения диаграммы и использующая этот тип данных. Помимо этого, имеются типы данных, которые могут быть преобразованы только явно:
- hierarchyid
- sql_variant
- XML
- CLR UDT
Вот скрипт, который выполняет указанные действия:
SELECT TABLE_NAME, STRING_AGG(concat(COLUMN_NAME,',''|'''),',') cols
FROM information_schema.columns
WHERE TABLE_NAME != 'dtproperties'
GROUP BY TABLE_NAME
Ниже приведены результаты выполнения кода. В качестве примера используется учебная база данных сайта.
Пункт 2 алгоритма
Формируем строку запроса, конкатенируя строки. Переменная @search будет содержать поисковую строку. Для примера в качестве поисковой строки используется небезызвестный Bismarck, т.е. мы будем искать текст содержащий слово Bismarck в любом месте строки. Сам поисковый запрос находится во втором столбце результирующего набора, а в первом мы будем выводить имя таблицы. Для нашей задачи имя таблицы в первом столбце не используется в дальнейшем. Но оно может потребоваться для расширения функциональности. Вот к чему мы пришли:
DECLARE @search varchar(200) ='bismarck';
WITH tbls AS
(SELECT TABLE_NAME, STRING_AGG(concat(COLUMN_NAME,',''|'''),',') cols
FROM information_schema.columns
WHERE TABLE_NAME != 'dtproperties'
GROUP BY TABLE_NAME)
SELECT TABLE_NAME, CONCAT('select ''',TABLE_NAME,''' table_name,* from ',
TABLE_NAME,' where concat(',cols,') like ''%', @search,'%''' ) stmt
FROM tbls;
И фрагмент результата:
Напомню, что мы исходим из того, что разделитель столбцов (у нас "|") не должен присутствовать в поисковой фразе. В противном случае, нужно этот символ заменить на другой (другие).
Пункт 3 алгоритма
Описываем и открываем курсор, источником данных для которого будет полученный ранее запрос. Дополнительно опишем две переменных, куда будут заноситься данные, извлекаемые при обходе курсора. Тут все ясно.
DECLARE @t VARCHAR(100);
DECLARE @s VARCHAR(2000);
DECLARE @search VARCHAR(200) ='bismarck';
DECLARE X CURSOR for
WITH tbls AS
(SELECT TABLE_NAME, STRING_AGG(concat(COLUMN_NAME,',''|'''),',') cols
FROM information_schema.columns
WHERE TABLE_NAME != 'dtproperties'
GROUP BY TABLE_NAME)
SELECT TABLE_NAME, CONCAT('select ''',TABLE_NAME,''' table_name,* from ',
TABLE_NAME,' where concat(',cols,') like ''%', @search,'%''' ) stmt
FROM tbls;
OPEN X;
Пункт 4 алгоритма, последний
Обходим курсор, выполняя сформированный оператор во втором столбце курсора. Вернее, не совсем тот. Чтобы убрать неинформативный вывод таблиц, в которых не было найдено совпадение с поисковой фразой, мы дополняем оператор проверкой наличия строк на выходе. В итоге будут получаться операторы такого вида:
if exists(select 'Battles' table_name,* from Battles
where concat(date,'|',name,'|') like '%bismarck%')
select 'Battles' table_name,* from Battles
where concat(date,'|',name,'|') like '%bismarck%';
Возможно, это не очень оптимальный прием, но недостатки компенсируются удобством.
Вот фрагмент кода, выполняющего обход курсора с выполнением поискового оператора:
FETCH X INTO @t,@s;
WHILE @@FETCH_STATUS = 0
begin
SET @s = CONCAT('if exists(',@s,') ',@s,';');
EXECUTE (@s);
FETCH X INTO @t,@s;
END
И, наконец, скрипт целиком:
DECLARE @t VARCHAR(100);
DECLARE @s VARCHAR(2000);
DECLARE @search VARCHAR(200) ='bismarck';
DECLARE X CURSOR for
WITH tbls AS
(SELECT TABLE_NAME, STRING_AGG(concat(COLUMN_NAME,',''|'''),',') cols
FROM information_schema.columns
WHERE TABLE_NAME != 'dtproperties'
GROUP BY TABLE_NAME)
SELECT TABLE_NAME, CONCAT('select ''',TABLE_NAME,''' table_name,* from ',
TABLE_NAME,' where concat(',cols,') like ''%', @search,'%''' ) stmt
FROM tbls;
OPEN X;
FETCH X INTO @t,@s;
WHILE @@FETCH_STATUS = 0
begin
SET @s = CONCAT('if exists(',@s,') ',@s,';');
EXECUTE (@s);
FETCH X INTO @t,@s;
END
CLOSE X;
DEALLOCATE X;
Результаты поиска Бисмарка представлены на рисунке ниже.
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой