Как найти таблицу в sql по имени

Как найти нужные таблицы и отобразить всю информацию в одном запросе SQL

Где-то месяц назад ко мне обратился коллега с просьбой помочь составить комплексный запрос (если это можно так назвать), который можно было бы скопировать и вставить в phpMyAdmin, для выборки всех данных из таблиц плагина, оставшихся после удаления сайта/сайтов из сети WordPress.

Что? Объясняю. Допустим, у нас есть сеть на WordPress, в которой имеется N число сайтов. Установлен сетевой плагин, который для каждого сайта создает собственную таблицу в базе данных. Таблицы имеют следующие имена: wp_table для первого сайта и wp_N_table для N-го сайта (например, wp_10_table для десятого сайта в сети). В нашем случае, сайт X, Y и Z были удалены, но таблицы остались. Задача: одним запросом получить данные из этих таблиц для дальнейшего анализа.

Я не считаю себя огромным специалистом SQL и мне данная задача изначально показалась если не невыполнимой, то уж точно не той, которую я мог бы решить за пару минут. Пришлось покопаться в сети и найти как такое решают другие. Ниже представлено мое видение потенциального решения данной проблемы. Не беру на себя ответственность утверждать, что это самое оптимальное решение, но оно позволило мне поближе познакомиться с процедурным расширением SQL.

Итак, приступим. Изначально я разбил задачу на несколько этапов:

Получить список всех таблиц от плагина
Найти «таблицы-сироты»
Получить данные из найденных таблиц

Чтобы всего этого добиться, мы будем использовать хранимые процедуры в SQL.

Хранимая процедура — объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.

Базовый синтаксис для них следующий:

С помощью DELIMITER $$ мы задаем последовательность символов, которая будет завершать хранимую процедуру, без этого при наборе первой же строчки SQL будет исполнять набранный код. Данная последовательность может быть произвольной. В конце $$ укажет на завершение процедуры.

Создаем процедуру GetAllTables() с помощью CREATE PROCEDURE , она будет иметь начало BEGIN и конец END .

CALL GetAllTables(); — это выполнение всей процедуры.

В теле GetAllTables() мы объявляем переменные с помощью инструкции DECLARE , задаем тип и значение по умолчанию:

В переменной v_finished мы будем хранить статус обработки, в v_table — текущую таблицу.

Далее мы будем использовать курсор, чтобы осуществить построчную обработку нашего запроса. Задаем курсор table_cursor, который будет получать название таблиц из базы данных, соответствующих определенной маске.

Указываем что мы будем делать, когда не найдем больше результатов. Обработчик ошибок объявляется следующим образом:

Здесь action может принимать значение CONTINUE или EXIT , которые указывают на то что нужно продолжить или прекратить исполнение кода при достижении определенных условий. В нашем случае условием является NOT FOUND (результатов больше нет), при достижении которого мы задаем значение переменной v_finished равной 1.

Теперь мы отобразим все таблицы, которые были найдены в базе данных по заданной маске. Для этого лишь надо выполнить SQL запрос:

Наверное, повторение одного запроса здесь и выше, когда мы задавали курсор, является не самым оптимальным решением, но зато оно понятно для начинающих и не требует каких-то углубленных знаний SQL.

Далее мы откроем установленный раннее курсор и задаем цикл get_data , где будем последовательно присваивать переменной v_table результаты нашего запроса.

Проверяем закончилась ли выборка и есть ли еще результаты. Напоминаю, что если результатов нет, то переменная v_finished будет равна 1. Это мы задавали выше. Если больше результатов нет, то мы выходим из цикла.

Здесь у меня возникла небольшая проблема: таблицы-то я нахожу, но как определить что именно данная таблица — это оставшаяся таблица от удаленного сайта. Как я писал выше, все таблицы в базе данных имеют вид wp_N_table. Нам лишь нужно получить значение N, присвоить его переменной v_id и посмотреть есть ли сайт с данным индексом в таблице wp_blogs. Но есть одно условие — у первого сайта в сети не будет индекса N, таблица будет иметь вид wp_table. Но, в то же время, сайт с индексом 1 все равно будет присутствовать в таблице wp_blogs. Чтобы избежать ошибок, мы сделаем небольшую проверку и установим v_id = 1 , если N не будет задана в названии таблицы:

Теперь осталось самое простое — для всех таблиц, индекс которых мы не нашли в wp_blogs, нужно выполнить запрос выборки данных и отобразить все это пользователю:

Чтобы подставить значение v_table в запрос, необходимо использовать команду concat, результат которой мы присваиваем переменной sql_query . Кстати, переменные в коде задаются с символом @ перед именем.

SQL Server: Search and Find Table by Name

There are several ways to search and find the tables in the SQL Server database by table name or by column name. Below are few of the methods to find table in the database. I’ve used these methods in SQL Server 2014 and they are working good.

I. Find Table By Table Name Querying sys.tables

The most common and simple method to find and list down the tables in a database based on the name of the table or a phrase is by using this simple select query against the system table sys.tables. If you are a sql expert then this will be the first option you will choose.

II. Find Table By Table Name Using Filter Settings in Object Explores

Another easiest method to find the tables by the table’s name in SQL Server database is to use the filter settings option in the object explorer in SQL Server Management Studio.

In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
Right Click the Tables folder and select Filter in the right-click menu.
Under filter, select Filter Settings. The filter settings window will pop up.
In the filter settings window against the property Name, you can choose one of the Operator from the list (Equals, Contains, Does not contain) and enter the search phrase (or table name) under Value and then click OK.
The list of tables under the Tables folder are filtered based on your filter criteria.

Поиск данных и объектов в базе данных MS SQL Server с помощью бесплатной утилиты dbForge Search

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

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

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

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

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

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

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

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

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

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

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

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

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

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

таблица sys.all_objects
скалярная функция schema_name

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

Быстрый поиск объектов и данных в БД SQL Server

Очень часто разработчики и администраторы БД сталкиваются с задачей поиска в базе данных всех упоминаний какого-либо объекта, столбца, переменной или поиск всех таблиц, где встречается искомое значение. Если вам приходилось решать подобную проблему, то вы знаете, что это ни самая тривиальная задача и Ctrl+F здесь не поможет.

Готового решения нет ни в SQL Server Management Studio ни в Visual Studio, вот несколько сценариев, которые вы можете использовать:

Поиск данных в таблицах и представлениях

Есть много реализаций на T-SQL поиска данных по всем таблицам с просмотром всех столбцов и это не самая оптимальная реализация, так как везде используется перебор в курсоре системных представлений.

У этого решения есть много недостатков:

Использование курсоров, а это, как правило неэффективный код
Сложный запрос, который медленно работает даже на небольших базах данных
Поиск работает только по текстовым данным, поэтому для поиска, например, даты потребуется доработка

Поиск объектов

Поиск объектов в БД по имени или их упоминание в других объектах немного проще, чем поиск определённого текста. Есть так же несколько разных сценариев поиска, но все их объединяет одно: обращение к системным объектам.

Во всех следующих сценариях осуществляется поиск переменной @StartProductID в хранимых процедурах. Но скрипты можно использовать и для поиска в других объектах – в триггерах, функциях, столбцах и т.д.

INFORMATION_SCHEMA.ROUTINES

Системное представление INFORMATION_SCHEMA.ROUTINES позволяет найти любой параметр, встречающийся в процедурах или функциях. Колонка ROUTINE_DEFINITION содержит полный текст объекта, который был указан при его создании.

Результат работы запроса:

Не используйте представления INFORMATION_SCHEMA, чтобы определить схему объекта. Единственный надежный способ найти схему объекта — выполнить запрос к представлению каталога sys.objects.

Представление sys.syscomments

Содержит записи для всех представлений, правил, значений по умолчанию, триггеров, ограничений CHECK и DEFAULT, а также для всех хранимых процедур в базе данных. Столбец text содержит инструкции исходных определений SQL.

Этот метод не желательно использовать, так как в будущих версиях SQL Server представление sys.syscomments будет удалено.

Представление sys.sql_modules

Содержит по одной строке для каждого объекта, являющегося модулем, определенным на языке SQL в SQL Server.

Результат такой же, как в предыдущем способе:

Другие представления информационной схемы

Запрос к представлениям sys.syscomments, sys.schemas и sys.objects. Представление sys.schemas содержит информацию обо всех схемах внутри базы данных. В представление sys.objects содержится информация обо всех объектах базы данных. Обратите внимание, что для поиска информации о триггерах необходимо просматривать отдельное представление sys.triggers.

Основным недостатком данных методов поиска является то, что для поиска каждого нового типа объектов необходимо вносить в скрипты изменения. Чтобы сделать это вы должны хорошо понимать внутреннюю организацию и структуру системных объектов SQL Server. Кроме того, нужно позаботиться об обработке различных ошибок и исключений, например, связанных с поиском строк, содержащих экранирующие символы.

Если вы не являетесь опытным разработчиком, не знакомы с внутренним устройством хранения DDL информации объектов БД или предпочитаете использовать проверенное и безошибочное решение, то начните использовать ApexSQL Search.

ApexSQL Search – это надстройка (ADD-IN) для SSMS и Visual Studio, которая позволяет искать любой текст в объектах базы данных (в том числе имена объектов), данные, хранящиеся в таблицах и представлениях (даже если они зашифрованы), осуществлять повторные поиски по истории в один клик.

Для поиска данных в таблицах и представлениях:

ApexSQL Search

Выберите вариант Database text search…:

ApexSQL Search - Database text search

После нажатия кнопки Find now, вы получите сводную таблицу со списком таблиц и представлений, которые содержат искомое значение:

ApexSQL Search - Database text search

ApexSQL Search - Database search details

Для поиска объектов: