Как найти нужные таблицы и поля в незнакомой базе данных SQL для интеграции с 1С?

← На главную

При интеграции 1С со сторонними учетными, кассовыми или ресторанными системами (например, Tillypad, R-Keeper, iiko и др.) разработчики часто сталкиваются с задачей прямого чтения данных из внешней СУБД Microsoft SQL Server. Главная сложность заключается в том, что структура базы данных может содержать сотни или тысячи таблиц с неочевидными названиями и сокращениями, а официальная документация по схеме данных часто устаревает либо отсутствует вовсе.

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

Почему поиск через внешние ключи (Foreign Keys) заходит в тупик

Проанализируем типичную ошибку при исследовании БД: попытку восстановить связи таблиц исключительно через системное представление sys.foreign_keys. Во многих прикладных системах целостность данных поддерживается на уровне логики приложения или триггеров, а физические ограничения внешних ключей в СУБД создаются далеко не для всех сущностей.

Кроме того, структура продаж обычно разделена на несколько уровней:

Заголовки чеков и транзакций: таблицы оплат, статусов и смен (например, BalanceChecks с префиксами blch_ или chgr_). Здесь фиксируются факт оплаты, суммы и номера документов, но отсутствует детальный состав товаров.
Строки чеков (позиции заказа): промежуточная таблица детализации, хранящая количество, цену, скидки и ссылки на идентификаторы блюд/товаров (часто используются префиксы ord_, pos_, dish_, item_).
Справочники номенклатуры: каталоги товаров, модификаторов и прейскурантов, где хранятся текстовые наименования и артикулы.

Если связь между строками и справочником не оформлена как явный FOREIGN KEY, стандартный запрос по системным связям покажет только оплату, но не выведет состав заказа. Рассмотрим более надежные методы поиска.

Способ 1. Поиск таблиц по ключевым словам в именах колонок

Вместо ручного просмотра сотен таблиц выполним поиск по системному представлению INFORMATION_SCHEMA.COLUMNS. Это позволяет мгновенно найти все таблицы, содержащие поля с признаками наименований, цен, товаров или номенклатуры.

Запустим в среде SQL Server Management Studio следующий запрос:


SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    COLUMN_NAME, 
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%NAME%' 
   OR COLUMN_NAME LIKE '%ITEM%' 
   OR COLUMN_NAME LIKE '%PROD%' 
   OR COLUMN_NAME LIKE '%DISH%' 
   OR COLUMN_NAME LIKE '%PRICE%'
ORDER BY TABLE_NAME;

Если требуется отфильтровать список таблиц по определенному шаблону названия (например, найти таблицы, связанные с чеками или позициями), используем поиск по представлению INFORMATION_SCHEMA.TABLES:


SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%Check%' 
   OR TABLE_NAME LIKE '%Order%' 
   OR TABLE_NAME LIKE '%Item%' 
   OR TABLE_NAME LIKE '%Sale%';

Способ 2. Трассировка запросов через SQL Server Profiler

Наиболее эффективный и точный способ обратной разработки схемы — перехват реальных запросов, которые генерирует клиентское приложение. Этот метод избавляет от необходимости угадывать назначение полей.

Разберем порядок действий:

Запустим утилиту SQL Server Profiler (или настроим сессию Extended Events в современных версиях MS SQL).
Создадим новый след трассировки и подключимся к рабочему экземпляру СУБД.
Во вкладке фильтров событий (Event Selection) настроим фильтр по имени базы данных или имени клиентского приложения (Application Name), чтобы отсечь системный шум.
В исходной программе (например, Tillypad или кассовом модуле) откроем отчет по продажам, сформируем чек или откроем карточку номенклатуры.
Остановим трассировку в профайлере и проанализируем события SQL:BatchCompleted или RPC:Completed.

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

Способ 3. Поиск текста в хранимых процедурах и представлениях

Во многих учетных системах разработчики создают готовые представления (Views) для формирования отчетов. Поищем упоминания известных нам таблиц (например, BalanceChecks) в тексте модулей базы данных через системную таблицу sys.sql_modules:


SELECT 
    OBJECT_SCHEMA_NAME(sm.object_id) AS SchemaName,
    OBJECT_NAME(sm.object_id) AS ObjectName,
    o.type_desc AS ObjectType,
    sm.definition
FROM sys.sql_modules sm
JOIN sys.objects o ON sm.object_id = o.object_id
WHERE sm.definition LIKE '%BalanceChecks%' 
   OR sm.definition LIKE '%chgr_ID%';

Этот запрос выведет все представления, хранимые процедуры и функции, где уже реализована бизнес-логика сборки данных о чеках и продажах.

Способ 4. Динамический поиск конкретного значения по всей базе данных

Если известно точное наименование редкого товара или блюда, можно выполнить сканирование текстовых колонок всех таблиц базы данных одним скриптом прямо на стороне SQL Server. Это выполняется за несколько секунд и исключает медленные циклы на стороне 1С.

Выполним следующий скрипт в SSMS, подставив искомую строку:


DECLARE @SearchStr NVARCHAR(100) = N'ТестовыйТовар';
DECLARE @TableName NVARCHAR(256);
DECLARE @ColumnName NVARCHAR(256);
DECLARE @Sql NVARCHAR(MAX);

DECLARE TableCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT 
        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME),
        QUOTENAME(COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
      AND TABLE_SCHEMA != 'sys';

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = N'IF EXISTS (SELECT 1 FROM ' + @TableName + N' WITH (NOLOCK) WHERE ' + @ColumnName + N' LIKE @Pattern) ' +
               N'PRINT ''Найдено в таблице: '' + ''' + @TableName + N''' + '', колонка: '' + ''' + @ColumnName + N''';';
    
    EXEC sp_executesql @Sql, N'@Pattern NVARCHAR(100)', @Pattern = N'%' + @SearchStr + N'%';
    
    FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;

Рекомендации по подключению к сторонней СУБД из 1С

После определения структуры таблиц для организации обмена данными в 1С учитываем следующие правила:

Не выполнять циклический перебор метаданных из кода 1С. Использование COM-объекта ADODB.Connection или внешних источников данных для перебора сотен таблиц в цикле 1С приводит к существенным задержкам из-за накладных расходов на сетевые вызовы. Всю аналитическую работу со структурой следует производить на стороне СУБД.
Использовать уровень изоляции без блокировок. При чтении оперативных таблиц сторонней базы из 1С обязательно добавляем табличную подсказку WITH (NOLOCK) к каждому источнику в тексте запроса. Это предотвратит взаимоблокировки и не нарушит работу кассовых терминалов и операторов.
Пример выполнения прямого запроса из 1С:


Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.ConnectionString = "Driver={SQL Server};Server=SQL_SRV;Database=TILLYPAD;Uid=sa;Pwd=SecretPass;";
Соединение.Open();

ТекстЗапроса = 
"SELECT TOP 100 
    bc.blch_ID,
    bc.blch_Total,
    it.dish_Name,
    it.dish_Price
FROM BalanceChecks bc WITH (NOLOCK)
JOIN OrderItems oi WITH (NOLOCK) ON bc.blch_ID = oi.ord_blch_ID
JOIN Dishes it WITH (NOLOCK) ON oi.ord_dish_ID = it.dish_ID
ORDER BY bc.blch_ID DESC";

Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
Команда.CommandText = ТекстЗапроса;

Выборка = Команда.Execute();
Пока Выборка.EOF() = 0 Цикл
    // Обработка полученных строк продажи
    ИмяТовара = Выборка.Fields("dish_Name").Value;
    Сумма = Выборка.Fields("blch_Total").Value;
    Выборка.MoveNext();
КонецЦикла;

Соединение.Close();

Комбинация поиска по системным метаданным INFORMATION_SCHEMA и трассировки через SQL Server Profiler позволяет быстро восстановить структуру связей любой закрытой базы данных и построить корректную интеграцию с 1С.