Дуже часто розробники і адміністратори БД стикаються із завданням пошуку в базі даних всіх згадувань будь-якого об'єкта, стовпці, змінної або пошук всіх таблиць, де зустрічається шукане значення. Якщо вам доводилося вирішувати подібну проблему, то ви знаєте, що це ні сама тривіальна задача і 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, яка дозволяє шукати будь-який текст в об'єктах бази даних (в тому числі імена об'єктів), дані, що зберігаються в таблицях і уявленнях (навіть якщо вони зашифровані), здійснювати повторні пошуки з історії в один клік.

Для пошуку даних в таблицях і уявленнях:

1. В меню SQL Server Management Studio або Visual Studio знайдіть ApexSQL Search

2. Виберіть варіант Database text search ...:

   

3. У текстовому полі пошуку Search text вкажіть шуканий текст.
4. У спадному меню Database виберіть базу даних для пошуку
5. У дереві пошуку Select objects to search вкажіть таблиці та подання для пошуку або залиште їх все виділеними
6. За допомогою прапорців вкажіть в яких типах даних необхідно здійснити пошук (numeric, text type, uniqueidentifier, date columns), чи шукати в уявленнях, чи потрібно суворе збіг і, при пошуку дати, вкажіть її формат.
   

   

7. Після натискання кнопки Find now, ви отримаєте зведену таблицю зі списком таблиць і уявлень, які містять шукане значення:

   

8. Натисніть кнопку з трьома крапками в колонці Column value, щоб отримати деталі:
   

   

Для пошуку об'єктів:

1. В меню SQL Server Management Studio або Visual Studio знайдіть ApexSQL Search

2. Виберіть варіант Database object search ...:

   

3. В поле пошуку Search text вкажіть шуканий об'єкт, наприклад, ім'я змінної.
4. У спадному меню Database виберіть базу даних для пошуку
5. У дереві пошуку Objects вкажіть типи об'єктів для пошуку або залиште їх все виділеними
6. Прапорцями вкажіть деталі пошуку: чи шукати в іменах об'єктів, колонок, індексів або тільки в самих опису об'єктів. Переглядати чи системні об'єкти, чи потрібно точний збіг, а також можна вказати екранує символ.
7. Після цього починаємо пошук Find now:
   

   

   У таблиці буде повний список об'єктів, які містять шукане значення.

8. При подвійному натисканні по об'єкту в таблиці Database object search, можна побачити його посилання в Object Explorer
   

   

SQL Server Management Studio та Visual Studio не мають вбудованої можливості пошуку об'єктів і даних в БД. Запити, які вирішують цю задачу неефективні, повільні в роботі і вимагають глибоких знань системних об'єктів SQL Server. Але зате з цим завданням чудово справляється ApexSQL Search

перекладач: Олексій Князєв

November 20, 2015