Описание общей потребности в поиске данных и объектов в базе данных


Поиск данных, а также хранимых процедур, таблиц и других объектов в базе данных является достаточно актуальным вопросом в том числе и для C#-разработчиков, а также и для .NET-разработки в целом.

Достаточно часто может возникнуть ситуация, при которой нужно найти:

  1. объект базы данных (таблицу, представление, хранимую процедуру, функцию и т д)
  2. данные (значение и в какой таблице располагается)
  3. фрагмент кода в определениях объектов базы данных

Существует множество готовых решений как платных, так и бесплатных.

Сначала рассмотрим как можно осуществлять поиск данных и объектов в базе данных с помощью встроенных средств самой СУБД, а затем рассмотрим как это сделать с помощью бесплатной утилиты dbForge Search.

Поиск с помощью встроенных средств самой СУБД


Определить есть ли таблица Employee в базе данных можно с помощью следующего скрипта:

Поиск таблицы по имени
select [object_id], [schema_id],
	   schema_name([schema_id]) as [schema_name], 
	   [name], 
	   [type], 
	   [type_desc], 
	   [create_date], 
	   [modify_date]
from sys.all_objects
where [name]='Employee';


Результат может быть примерно такой:



Здесь выводятся:

  1. идентификаторы объекта и схемы, где располагается объект
  2. название этой схемы и название этого объекта
  3. тип объекта и описание этого типа объекта
  4. даты и время создания и последней модификации объекта

Чтобы найти все вхождения строки “Project”, то можно использовать следующий скрипт:

Поиск всех объектов по подстроке в имени
select [object_id], [schema_id],
	   schema_name([schema_id]) as [schema_name], 
	   [name], 
	   [type], 
	   [type_desc], 
	   [create_date], 
	   [modify_date]
from sys.all_objects
where [name] like '%Project%';


Результат может быть примерно такой:



Как видно из результата, здесь подстроку “Project” содержат не только две таблицы Project и ProjectSkill, но и также некоторые первичные и внешние ключи.

Чтобы понять кому именно принадлежат данные ключи, добавим в вывод поле parent_object_id и его имя и схему, в которой он располагается следующим образом:

Поиск всех объектов по подстроке в имени с выводом родительских объектов
select ao.[object_id], ao.[schema_id],
	   schema_name(ao.[schema_id]) as [schema_name],
	   ao.parent_object_id,
	   p.[schema_id] as [parent_schema_id],
	   schema_name(p.[schema_id]) as [parent_schema_name],
	   p.[name] as [parent_name],
	   ao.[name], 
	   ao.[type], 
	   ao.[type_desc], 
	   ao.[create_date], 
	   ao.[modify_date]
from sys.all_objects as ao
left outer join sys.all_objects as p on ao.[parent_object_id]=p.[object_id]
where ao.[name] like '%Project%';


Результатом будет вывод таблицы с детальной информацией о родительских объектах, т е где определены первичные и внешние ключи:



В запросах используются следующие системные объекты:


Итак, разобрали как найти объекты в базе данных с помощью встроенных средств самой СУБД.
Теперь покажем как найти данные в базе данных на примере поиска строк.

Чтобы найти строковое значение по всем таблицам базы данных, можно воспользоваться следующим решением. Упростим данное решение и покажем как можно найти например значение “Ramiro” с помощью следующего скрипта:

Поиск строковых значений по подстроке во всех таблицах базы данных
set nocount on
declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
set @substr = '%Ramiro%'

declare @sql nvarchar(max);

create table #rslt 
(table_name varchar(128), field_name varchar(128), [value] nvarchar(max))

declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
open s
fetch next from s into @name
while @@fetch_status = 0
begin
 declare c cursor for 
	select quotename(column_name) as column_name from information_schema.columns 
	  where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname', 'int', 'tinyint') and table_name  = @name
 set @name = quotename(@name)
 open c
 fetch next from c into @column
 while @@fetch_status = 0
 begin
   --print 'Processing table - ' + @name + ', column - ' + @column

   set @sql='insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', cast(' + @column + 
	' as nvarchar(max)) from' + @name + ' where cast(' + @column + ' as nvarchar(max)) like ''' + @substr + '''';

	print @sql;

   exec(@sql);

   fetch next from c into @column;
 end
 close c
 deallocate c
 fetch next from s into @name
end
select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
group by table_name, field_name
order by table_name, field_name

drop table #rslt
close s
deallocate s


Результат выполнения может быть таким:



Здесь выводятся имена таблиц и в каких столбцах хранится значение, содержащие подстроку “Ramiro”. А также количество найденных входов данной подстроки для найденной пары таблица-колонка.

Чтобы найти объекты, в определениях которых есть заданный фрагмент кода, можно воспользоваться следующими системными представлениями:

  1. sys.sql_modules
  2. sys.all_sql_modules
  3. sys.syscomments

Например, используя последнее представление, можно с помощью следующего скрипта найти все объекты, в определениях которых встречается заданный фрагмент кода:

Поиск фрагмента кода в определениях объектов базы данных
select obj.[object_id],
	   obj.[name],
	   obj.[type_desc],
	   sc.[text]
from sys.syscomments as sc
inner join sys.objects obj on sc.[id]=obj.[object_id]
where sc.[text] like '%code snippet%';


Здесь будет выведен идентификатор, название, описание и полное определение объекта.

Поиск с помощью бесплатной утилиты dbForge Search


Однако, более удобно поиск производить с помощью готовых хороших инструментов. Одним из таких инструментов является dbForge Search.

Для вызова этой утилиты в окне SSMS нажмите на кнопку .

Появится следующее окно поиска:



Обратите внимание на верхнюю панель (слева направо):

  1. можно переключать режим поиска (ищем DDL (объекты) или данные)
  2. непосредственно что ищем (какую подстроку)
  3. учитывать ли регистр, искать точное соответствие слову, искать вхождения:


  4. группировать результат по типам объектов — кнопка
  5. выбрать нужные типы объектов для поиска:

  6. также можно задать несколько баз данных для поиска и выбрать экземпляр MS SQL Server

Это все в режиме поиска объектов, т е когда включен DDL:



В режиме поиска данных изменится только выбор типов объектов:



А именно будут доступны для выбора только таблицы, где и хранятся собственно сами данные:



Теперь как и раньше найдем все вхождения подстроки “Project” в названиях объектов:



Как видно, был выбран режим поиска по DDL-объектам, заполнено что ищем-строка “Project”, остальное все было по умолчанию.

При выделении найденного объекта внизу отображается код определения данного объекта или всего его родительского объекта.

Также можно переместить навигацию на найденный объект, щелкнув на кнопку :



Можно также сгруппировать найденные объекты по их типу:



Обратите внимание, что выводятся даже те таблицы, в которых есть поля, в именах которых содержится подстрока “Project”. Однако, напомним, что режим поиска можно менять: искать полное соответствие/частичное/учитывать регистр или нет.

Теперь найдем значение “Ramiro” по всем таблицам:



Обратите внимание, что внизу отображаются все строки, в которых содержится подстрока “Ramiro” выбранной таблицы Employee.

Также можно переместить навигацию к найденному объекту, нажав как и ранее на кнопку :



Таким образом мы можем искать нужные объекты и данные в базе данных.

Заключение


Были рассмотрены способы поиска как самих данных, так и объектов в базе данных как с помощью встроенных средств самой СУБД MS SQL Server, так и с помощью бесплатной утилиты dbForge Search.

Также от компании Devart есть и ряд других бесплатных готовых решений, полный список которых можно посмотреть здесь.

Источники


Комментарии (8)


  1. rv82
    12.11.2019 13:51
    +2

    Года 2 назад пробовал эту штуку, но она почему-то ни в какую не хотела переходить к найденному объекту в Object explorer'е. В итоге бросил её и скачал с их же сайта dbForge Express, тоже бесплатный. С тех пор так и пользуюсь двумя программами. SSMS использую, если нужен пошаговый отладчик или если приходится работать с большим sql-файлом. У SSMS весьма удобный инкрементальный поиск, чем не может похвастать dbForge. Для всего остального использую dbForge. В частности, если нужно влезть грубой силой в какую-то таблицу и что-то в ней поменять. Редактор данных в dbForge очень удобен.Жаль, нет такого в SSMS.

    И да, в SSMS есть своё средство для поиска объектов а Object Explorer'е. Называется Object explorer details и вызывается клавишей F7. Находит объект по имени и позволяет отобразить его в Object Explorer'е. По содержимому объектов не ходит. Поэтому, насколько я понял, найти, какой таблице принадлежит столбец или где вызывается определённая функция, эта штука не сможет, в отличии от dbForge.


    1. jobgemws Автор
      12.11.2019 13:58
      +1

      Да, Вы правы, раньше были определенные проблемы с этим инструментом.
      Но сейчас все исправили и потому решил о нем описать здесь


      1. rv82
        12.11.2019 14:00
        +1

        Спасибо за Вашу статью! Обязательно скачаю и проверю.


        1. jobgemws Автор
          12.11.2019 14:06
          +1

          Очень удобная-советую.
          Однако, если найдете какие-то недочеты, то пишите прямо сюда со скринами.
          Также по всем проблемам и пожеланиям можно обратиться непосредственно к производителю.
          Как раз ту проблему с переходом поправили в том числе и по моей заявке пусть и не быстро, но поправили. Т е обратная связь работает)


  1. toxicdream
    13.11.2019 09:35
    +1

    А где сравнение с другими решениями?
    Взять, хотя бы, тот же ApexSQL Search.


    1. jobgemws Автор
      13.11.2019 10:37

      Цель статьи-не сравнивать существующие бесплатные инструменты, а показать как это можно сделать встроенными средствами СУБД и с помощью конкретного бесплатного инструмента.
      Однако, Вы можете написать свою статью и сравнить разные бесплатные решения. Думаю читателям будет интересно


    1. askerov_o
      13.11.2019 11:53
      +1

      А ещё вы можете почитать уже готовую статью про бесплатные решения ApexSQL. Есть ещё пара бесплатных от Solarwinds, о них тут.


      1. jobgemws Автор
        13.11.2019 12:05

        Ваша статья как раз сподвигла меня написать про поиск стандартными средствами СУБД и через один из бесплатных инструментов.
        Спасибо за стимул)