Новости
Дуже часто розробники і адміністратори БД стикаються із завданням пошуку в базі даних всіх згадувань будь-якого об'єкта, стовпці, змінної або пошук всіх таблиць, де зустрічається шукане значення. Якщо вам доводилося вирішувати подібну проблему, то ви знаєте, що це ні сама тривіальна задача і 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); SET @SearchText = 'John'; DECLARE CursorSearch CURSOR FOR SELECT name, object_id FROM sys .objects WHERE type = 'U'; 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); 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