Очень часто разработчики и администраторы БД сталкиваются с задачей поиска в базе данных всех упоминаний какого-либо объекта, столбца, переменной или поиск всех таблиц, где встречается искомое значение. Если вам приходилось решать подобную проблему, то вы знаете, что это ни самая тривиальная задача и Ctrl+F здесь не поможет.
Готового решения нет ни в SQL Server Management Studio ни в Visual Studio, вот несколько сценариев, которые вы можете использовать:
Поиск данных в таблицах и представлениях
Есть много реализаций на T-SQL поиска данных по всем таблицам с просмотром всех столбцов и это не самая оптимальная реализация, так как везде используется перебор в курсоре системных представлений.
DECLARE @SearchText varchar(200), @Table varchar(100), @TableID int, @ColumnName varchar(100), @String varchar(1000); --modify the variable, specify the text to search for SET @SearchText = 'John'; DECLARE CursorSearch CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'; --list of tables in the current database. Type = 'U' = tables(user-defined) OPEN CursorSearch; FETCH NEXT FROM CursorSearch INTO @Table, @TableID; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE CursorColumns CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @TableID AND system_type_id IN(167, 175, 231, 239); -- the columns that can contain textual data --167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar OPEN CursorColumns; FETCH NEXT FROM CursorColumns INTO @ColumnName; WHILE @@FETCH_STATUS = 0 BEGIN SET @String = 'IF EXISTS (SELECT * FROM ' + @Table + ' WHERE ' + @ColumnName + ' LIKE ''%' + @SearchText + '%'') PRINT ''' + @Table + ', ' + @ColumnName + ''''; EXECUTE (@String); FETCH NEXT FROM CursorColumns INTO @ColumnName; END; CLOSE CursorColumns; DEALLOCATE CursorColumns; FETCH NEXT FROM CursorSearch INTO @Table, @TableID; END; CLOSE CursorSearch; DEALLOCATE CursorSearch;
У этого решения есть много недостатков:
- Использование курсоров, а это, как правило неэффективный код
- Сложный запрос, который медленно работает даже на небольших базах данных
- Поиск работает только по текстовым данным, поэтому для поиска, например, даты потребуется доработка
Поиск объектов
Поиск объектов в БД по имени или их упоминание в других объектах немного проще, чем поиск определённого текста. Есть так же несколько разных сценариев поиска, но все их объединяет одно: обращение к системным объектам.
Во всех следующих сценариях осуществляется поиск переменной @StartProductID в хранимых процедурах. Но скрипты можно использовать и для поиска в других объектах – в триггерах, функциях, столбцах и т.д.
INFORMATION_SCHEMA.ROUTINES
Системное представление INFORMATION_SCHEMA.ROUTINES позволяет найти любой параметр, встречающийся в процедурах или функциях. Колонка ROUTINE_DEFINITION содержит полный текст объекта, который был указан при его создании.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%@StartproductID%' AND ROUTINE_TYPE='PROCEDURE'
Результат работы запроса:
Не используйте представления INFORMATION_SCHEMA, чтобы определить схему объекта. Единственный надежный способ найти схему объекта — выполнить запрос к представлению каталога sys.objects.
Представление sys.syscomments
Содержит записи для всех представлений, правил, значений по умолчанию, триггеров, ограничений CHECK и DEFAULT, а также для всех хранимых процедур в базе данных. Столбец text содержит инструкции исходных определений SQL.
SELECT OBJECT_NAME( id ) FROM SYSCOMMENTS WHERE text LIKE '%@StartProductID%' AND OBJECTPROPERTY(id , 'IsProcedure') = 1 GROUP BY OBJECT_NAME( id );
Результат:
Этот метод не желательно использовать, так как в будущих версиях SQL Server представление sys.syscomments будет удалено.
Представление sys.sql_modules
Содержит по одной строке для каждого объекта, являющегося модулем, определенным на языке SQL в SQL Server.
SELECT OBJECT_NAME( object_id ) FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id , 'IsProcedure') = 1 AND definition LIKE '%@StartProductID%';
Результат такой же, как в предыдущем способе:
Другие представления информационной схемы
Запрос к представлениям sys.syscomments, sys.schemas и sys.objects. Представление sys.schemas содержит информацию обо всех схемах внутри базы данных. В представление sys.objects содержится информация обо всех объектах базы данных. Обратите внимание, что для поиска информации о триггерах необходимо просматривать отдельное представление sys.triggers.
DECLARE @searchString nvarchar( 50 ); SET@searchString = '@StartProductID'; SELECT DISTINCT s.name AS Schema_Name , O.name AS Object_Name , C.text AS Object_Definition FROM syscomments C INNER JOIN sys.objects O ON C.id = O.object_id INNER JOIN sys.schemas S ON O.schema_id = S.schema_id WHERE C.text LIKE '%' + @searchString + '%' OR O.name LIKE '%' + @searchString + '%' ORDER BY Schema_name , Object_name;
Полученный результат:
Основным недостатком данных методов поиска является то, что для поиска каждого нового типа объектов необходимо вносить в скрипты изменения. Чтобы сделать это вы должны хорошо понимать внутреннюю организацию и структуру системных объектов SQL Server. Кроме того, нужно позаботиться об обработке различных ошибок и исключений, например, связанных с поиском строк, содержащих экранирующие символы.
Если вы не являетесь опытным разработчиком, не знакомы с внутренним устройством хранения DDL информации объектов БД или предпочитаете использовать проверенное и безошибочное решение, то начните использовать ApexSQL Search.
ApexSQL Search – это надстройка (ADD-IN) для SSMS и Visual Studio, которая позволяет искать любой текст в объектах базы данных (в том числе имена объектов), данные, хранящиеся в таблицах и представлениях (даже если они зашифрованы), осуществлять повторные поиски по истории в один клик.
Для поиска данных в таблицах и представлениях:
- В меню SQL Server Management Studio или Visual Studio найдите ApexSQL Search
-
Выберите вариант Database text search…:
- В текстовом поле поиска Search text укажите искомый текст.
- В раскрывающемся меню Database выберите базу данных для поиска
- В дереве поиска Select objects to search укажите таблицы и представления для поиска или оставьте их все выделенными
- С помощью флажков укажите в каких типах данных необходимо осуществить поиск (numeric, text type, uniqueidentifier, date columns), искать ли в представлениях, необходимо ли строгое совпадение и, при поиске даты, укажите её формат.
-
После нажатия кнопки Find now, вы получите сводную таблицу со списком таблиц и представлений, которые содержат искомое значение:
- Нажмите кнопку с многоточием в колонке Column value, чтобы получить детали:
Для поиска объектов:
- В меню SQL Server Management Studio или Visual Studio найдите ApexSQL Search
-
Выберите вариант Database object search…:
- В поле поиска Search text укажите искомый объект, например, имя переменной.
- В раскрывающемся меню Database выберите базу данных для поиска
- В дереве поиска Objects укажите типы объектов для поиска или оставьте их все выделенными
- Флажками укажите детали поиска: искать ли в именах объектов, колонок, индексов или только в самих описания объектов. Просматривать ли системные объекты, нужно ли точное совпадение, а также можно указать экранирующий символ.
- После этого начинаем поиск Find now:
В таблице будет полный список объектов, которые содержат искомое значение.
- При двойном щелчке по объекту в таблице Database object search, можно увидеть его ссылку в Object Explorer
SQL Server Management Studio и Visual Studio не имеют встроенной возможности поиска объектов и данных в БД. Запросы, которые решают эту задачу неэффективны, медленные в работе и требуют глубоких знаний системных объектов SQL Server. Но зато с этой задачей прекрасно справляется ApexSQL Search
Переводчик: Алексей Князев
November 20, 2015
