SQL-Ex blog

Решение

Алгоритм

1. Для каждой таблицы конкатенируем текстовые столбцы с некоторым разделителем, которого заведомо не может быть в поисковой строке. Это позволит нам избежать ситуации, когда искомый фрагмент текста может начинаться в одном столбце строки таблицы, а заканчиваться - в другом.


2. Для каждой таблицы формируем строку запроса для динамического выполнения.


3. Полученный в результате первых двух пунктов запрос будем использовать в качестве источника строк для курсора.


4. При обходе курсора будем динамически выполнять построенный оператор. Чтобы избежать вывода всех таблиц, дополним скрипт критерием существования искомых строк в результатах поиска. Тогда на выходе будут только те строки/таблицы, в которых содержится искомая подстрока.

Пункт 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;

Результаты поиска Бисмарка представлены на рисунке ниже.