Как найти таблицу в sql из 1с

Обновлено: 22.01.2025

Решил написать статью о том, как вытягивать данные из 1С путем SQL запросов. Все нижесказанное касается 1С версии 8.2, оно также должно работать и в 1С версии 8.1. Особое внимание уделено проблеме с извлечением заголовков перечислений.

В идеале выборку данных из 1С должен делать 1С-программист. Хорошо, если он создаст обработку, которая выдаст данные в так называемую «буферную базу»: csv файлы, таблицы в SQL – что угодно. Проектировщик ХД и ETL должен брать данные из буфера.

В этом случае все работает предельно хорошо: зоны ответственности разделены, если найдена ошибка в данных отчета – ее вначале ищут в кубе, если в кубе все ОК – ищут в ХД, если в ХД все ОК – ищут в ETL, если в ETL все хорошо – значит пускай 1С-программист сам разбирается где у него ошибка в обработке, заполняющей «буферную БД».

Но не всегда такой способ доступен. Бывает, что 1С-специалиста либо вообще нет, либо слишком занят, либо мощностей железа не хватает, чтобы «выталкивать» данные из 1С с помощью обработки. И остается одно – делать извлечение данных с помощью SQL запросов.

Вот это собственно и есть этот способ – «сделать SQL запрос на 1С-базу». Главная задача – корректно написать сами запросы. Я думаю, ни для кого не есть секретом, что в 1С структура данных «хитрая», и что поля и таблицы имеют замысловатые названия. Задача проектировщика ETL – вытянуть данные из этой структуры.

Просмотр метаданных

Существуют обработки, дающие возможность просмотреть то, какие поля справочников/документов в каких таблицах/полях базы данных находятся.

Здесь Вы можете скачать несколько таких обработок (которые мы «отфильтровали» путем перебора десяток подобных, выбрав наилучшие). Они делают почти одно и то же – позволяют посмотреть все поля, понять какое поле на какой справочник ведет, и даже предлагают автоматически построить запрос:

Таким образом, начинаем исследовать нужные нам документы:

Дальше открываем любой из них, и находим то, куда он записывается – в какие регистры:

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

Мы делаем как правило два уровня SQL запросов: «нижний уровень» — вьюхи для переименования полей, «верхний уровень» – вьюхи, которые берут данные из нижнего уровня, и уже они делают необходимые джойны.

Перечисления

Есть одна большая проблема – это перечисления. Пример:

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

Да, мы нашли где заголовки перечислений сидят: таблица называется Config, в ней – image поля, в которых сидит зазипованный набор байт, который если раззиповать – получим непонятной структуры набор символов, разделителей и т.д. К сожалению, этот формат данных не документирован.

Вы можете скачать ее отсюда.

Запускается вот так:

Делает следующее: коннектится к 1С с помощью COM, берет оттуда все перечисления, и кладет их в указанную вами таблицу указанной базы, предварительно почистив ее. Таблица должна иметь следующую структуру

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

Если у Вас будут замечания или дополнительные идеи – все они с радостью принимаются, пишите на ibobak at bitimpulse dot com.

Здравствуйте!
Подскажите, как найти таблицу из 1с в sql?

Синтаксис:
ПолучитьСтруктуруХраненияБазыДанных(<ОбъектыМетаданных>, <ИменаБазыДанных>)
Параметры:
Тип: Массив.
Массив имен объектов метаданных или массив объектов метаданных, для которых требуется получить структуру таблиц базы данных.
Тип: Булево.
Определяет, в каких терминах выдается информация о структуре хранения.
Истина - в терминах СУБД.
Ложь - в терминах модели базы данных 1С:Предприятия.
Значение по умолчанию: Ложь.
Возвращаемое значение:
Тип: ТаблицаЗначений.
Возвращает таблицу значений с описаниями структуры таблиц, индексов и полей базы данных в терминах модели базы данных 1С:Предприятия или используемой СУБД, в зависимости от значения параметра <ИменаБазыДанных>.
Если параметр не используется, то возвращаемая таблица значений содержит информацию о структуре таблиц базы данных всех объектов метаданных.
Таблица значений включает следующие колонки:

Примечание. Количество и состав таблиц, полей и индексов могут отличаться в зависимости от значения параметра <ИменаБазыДанных>.
Описание:
Получает информацию о структуре таблиц базы данных для переданных в качестве параметра массива имен объектов метаданных или массива объектов метаданных для административных действий с ней.
Доступность:
Сервер, толстый клиент, внешнее соединение.
Примечание:
Следует использовать метод только для административных задач обслуживания базы данных и анализа записей технологического журнала. Не следует применять метод для реализации какой-либо части прикладной функциональности.
При изменениях в версиях часть изменений выполняется не сразу, а во время реструктуризации объектов или полной рекструктуризации. Такие изменения отмечаются в списке изменений. Метод возвращает структуру, которая будет получена после реструктуризации.
Пример:
СтруктБД=ПолучитьСтруктуруХраненияБазыДанных(МассивИменМетаданных);
Использование в версии:
Доступен, начиная с версии 8.3.1.

Здравствуйте!
Подскажите, как найти таблицу из 1с в sql?

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

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

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

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

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

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

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

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

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

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

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

Поиск всех объектов по подстроке в имени с выводом родительских объектов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

Читайте также: