Поиск строки во всех таблицах базы данных SQL Server

Быстрый ответ

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

DECLARE @SearchStr nvarchar(100) = N'ИскомаяСтрока';
DECLARE @Results TABLE (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(4000));

SET NOCOUNT ON;

DECLARE @Cursor CURSOR FOR
SELECT t.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE' AND c.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext');

OPEN @Cursor;

FETCH NEXT FROM @Cursor INTO @TableName, @ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('INSERT INTO @Results (TableName, ColumnName, ColumnValue)
          SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 4000) 
          FROM ' + @TableName + ' (NOLOCK)
          WHERE ' + @ColumnName + ' LIKE ''%' + @SearchStr + '%''');

    FETCH NEXT FROM @Cursor INTO @TableName, @ColumnName;
END;

CLOSE @Cursor;
DEALLOCATE @Cursor;

SELECT * FROM @Results;

Замените 'ИскомаяСтрока' на текст, который нужно найти, выполните скрипт и проверьте результаты в @Results.

Углубляемся: оптимизация и безопасность поиска

Ускорение работы благодаря выборочному просмотру баз данных

Чтобы ускорить процесс поиска, ограничьте его определёнными базами данных с использованием оператора USE.

Подробнее об этом расскажет наш спикер на видео

Безопасность динамического SQL

Функция QUOTENAME поможет корректно экранировать элементы динамического запроса и обезопасит систему от SQL-инъекций.

Улучшение производительности с помощью NOLOCK

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

Работа с большими базами данных

Большие базы данных требуют разбиения задачи на части или ограничения длины обрабатываемых строк с целью оптимизации производительности и предотвращения выполнения тяжёлых запросов.

Визуализация

Процесс работы системы поиска можно представить как поход по разделам библиотеки (таблицам) в поисках нужной книги (строки).

🏥 Библиотека Базы Данных 🏥
| Раздел (Таблица) | Статус 🕵️‍♂️ |
| ----------------- | ---------------- |
| Художественная литература | Проверено |
| Научно-популярная литература | Проверено |
| Руководства и справочники | Проверено |
| Энциклопедии | Найдено! |

Использование встроенных возможностей SQL Server

Использование системных представлений

Системные представления, такие как sys.objects и sys.schemas, позволяют ограничить поиск до пользовательских таблиц.

Адаптация под типы данных

Необходимо отфильтровать столбцы по их типам для того чтобы не тратить ресурсы на поиск в численных данных, которые не могут содержать текст.

Проверка результатов

@Results позволит убедиться в точности и полноте результатов поиска.

Продвинутые советы

Поиск с помощью сторонних инструментов

Есть инструменты от сторонних разработчиков, такие как ApexSQL Search, которые были созданы для упрощения процесса поиска.

Совместимость с версией SQL Server

Обязательно проверьте поддержку табличных переменных и других используемых элементов скрипта на вашем SQL Server.

Исполнение динамического SQL

Для обеспечения дополнительной безопасности используйте инструкцию sp_executesql при работе с динамическим SQL.

Освобождение ресурсов

Как и любые ресурсы, требующие значительных затрат, курсоры необходимо закрывать и освобождать после окончания их использования.

Полезные материалы

1. Поиск во всех таблицах базы данных SQL Server.
2. Подробное руководство по динамическому SQL для поиска информации в базе данных.
3. Улучшение надёжности и гибкости системных хранимых процедур.
4. Превращение SQL-запросов в мощный инструмент для извлечения данных из базы данных.
5. Общее руководство по обнаружению информации в SQL Server.
6. Оптимизация структуры таблиц в InnoDB для MySQL.