1С 8.3 Набор скриптов SQL (Шпаргалка) - Программист 1С Минск. Автоматизация бизнеса.

Перейти к контенту

1С 8.3 Набор скриптов SQL (Шпаргалка)

Данные > Примеры кода 1С > 1С 8.3 Приложение, система, интерфейс
SQL или язык структурированных запросов это декларативный язык программирования, который используют для создания, обработки и хранения данных в реляционных БД. Система управления базами данных или SQL Server обеспечивает хранение баз данных и доступ к ним из программ 1С. Подключение SQL необходимо при работе в 1С в клиент-серверном режиме, это позволяет оптимизировать работу большого количества пользователей с большим объёмом информации, за счет переноса ресурсоёмких операций на сервер. Используется разработчиками 1С для администрирования информационных баз, написания запросов, интеграций.
📰 Набор скриптов для знакомства с MS SQL Server:
Cбросить пароль в базе 1C:
-- Переименовать таблицу v8users
EXEC sp_rename 'v8users', 'v8users_tmp'
GO

-- Переименовать users.usr в users.usr_tmp
UPDATE Params
SET FileName = 'users.usr_tmp'
WHERE FileName = 'users.usr'
GO  

-- Создать пустую таблицу v8users
create table v8users(
ID binary(16) NOT NULL,
Name nvarchar(64) NOT NULL,
Descr nvarchar(128) NOT NULL,
OSName nvarchar(128) NULL,
Changed datetime2(0) NOT NULL,
RolesID numeric(10,0) NOT NULL,
Show binary(1) NOT NULL,
Data varbinary(max) NOT NULL,
EAuth binary(1) NULL,
AdmRole binary(1) NULL,
UsSprH numeric(10,0) NULL,
PRIMARY KEY (ID)
);

Базовая информация о сервере:
-- Имена сервера и экземпляра
Select @@SERVERNAME as [Server\Instance];

-- версия SQL Server
Select @@VERSION as SQLServerVersion;

-- экземпляр SQL Server
Select @@ServiceName AS ServiceInstance;

-- Текущая БД (БД, в контексте которой выполняется запрос)
Select DB_NAME() AS CurrentDB_Name;

Теперь мы знаем какая версия SQL Server используется, имя экземпляра и сервера.
Хотя есть подозрение, что имя экземпляра Вы знали еще до подключения к нему 🙂
Версия СУБД может пригодиться для определения какие возможности у нее есть, известные баги и др.


Время работы с момента запуска:
В большинстве вопросов диагностики производительности и стабильности важно знать сколько времени уже запущен экземпляр SQL Server.

SELECT  @@Servername AS ServerName ,
       create_date AS  ServerStarted ,
       DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning ,
       DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig
FROM    sys.databases
WHERE   name = 'tempdb';

Здесь мы получили имя сервера, дату его запуска, а также продолжительность работы сервера в днях и секундах. Для этого мы использовали дату создания базы TempDB, которая создается в момент запуска SQL Server.
Почему эта информация так важна? Сам SQL Server собирает статистику различных показателей (как используются индексы, информацию о недостающих индексах, планы запросов и др.). Чем дольше сервер работает, тем точнее собранная статистика.
Представьте, что Вы запустили сервер 5 минут назад, смотрите что информации о недостающих индексов нет. Правильно ли было полагаться на эти данные, которые были собраны за 5 минут?


Количество активных соединений:
Еще одним очень важным моментом может быть количество активных соединений со SQL Server.
-- Похожая информация, может быть получена с помощью sp_who

SELECT  @@Servername AS Server ,
       DB_NAME(database_id) AS DatabaseName ,
       COUNT(database_id) AS Connections ,
       Login_name AS  LoginName ,
       MIN(Login_Time) AS Login_Time ,
       MIN(COALESCE(last_request_end_time, last_request_start_time))
                                                        AS  Last_Batch
FROM    sys.dm_exec_sessions
WHERE   database_id > 0
       AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' )
GROUP BY database_id ,
        login_name
ORDER BY DatabaseName;

Эта информация может пригодиться в следующих случаях:
Отслеживание количества соединений для контроля лицензионной "чистоты".
Для контроля подключения к базе данных.
Для сбора статистики интенсивности работы с СУБД.
Расследование проблем "зависших" соединений.
И др.

Получить список всех баз на сервере:
Все варианты дают примерно ту же самую информацию, но с разной детализацией.

Способ 1:
EXEC sp_helpdb;

Способ 2:
EXEC sp_Databases;

Способ 3:
SELECT  @@SERVERNAME AS Server ,
       name AS DBName ,
       recovery_model_Desc AS RecoveryModel ,
       Compatibility_level AS CompatiblityLevel ,
       create_date ,
       state_desc
FROM    sys.databases
ORDER BY Name;

Способ 4:
SELECT  @@SERVERNAME AS Server ,
       d.name AS DBName ,
       create_date ,
       compatibility_level ,
       m.physical_name AS FileName
FROM    sys.databases d
       JOIN sys.master_files m ON d.database_id = m.database_id
WHERE   m.[type] = 0 -- data files only
ORDER BY d.name;

Информация о бэкапах:
Резервное копирование - очень важно. Об этом уже так много было сказано, но случаев когда им пренебрегают до сих пор очень много. Узнаем дату последнего бэкапа тех баз, которые расположены на изучаемом сервере.

SELECT  @@Servername AS ServerName ,
       d.Name AS DBName ,
       MAX(b.backup_finish_date) AS LastBackupCompleted
FROM    sys.databases d
       LEFT OUTER JOIN msdb..backupset b
                   ON b.database_name = d.name
                      AND b.[type] = 'D'
GROUP BY d.Name
ORDER BY d.Name;

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

SELECT  @@Servername AS ServerName ,
       d.Name AS DBName ,
       b.Backup_finish_date ,
       bmf.Physical_Device_name
FROM    sys.databases d
       INNER JOIN msdb..backupset b ON b.database_name = d.name
                                       AND b.[type] = 'D'
       INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
ORDER BY d.NAME ,
       b.Backup_finish_date DESC;

Теперь мы знаем есть ли настроенное резервное копирование. Ну или хотя бы ручной запуск бэкапирования.

Выгрузить и загрузить бэкап с дальнейшим удалением бэкап файла t-sql:
// Объявление переменных
DECLARE @from NVARCHAR(MAX);
DECLARE @from_log NVARCHAR(MAX);
DECLARE @to NVARCHAR(MAX);
DECLARE @to_mdf NVARCHAR(MAX);
DECLARE @to_log NVARCHAR(MAX);
DECLARE @buckap_result NVARCHAR(MAX);
DECLARE @buckap_folder NVARCHAR(MAX);
DECLARE @BackupSetID INT;
DECLARE @alterSingl NVARCHAR(MAX);
DECLARE @alterMulti NVARCHAR(MAX);

// Установка значений переменных
SET @from  = N'ИмяБазыОтКуда'; -- что бэкапим
SET @to  = N'ИмяБазыКуда'; -- куда востанавливаем
SET @from_log  = @from+'_log'; -- имя лог файла источника
SET @buckap_folder  =  N'D:\Base\MSSQL14.MSSQLSERVER\MSSQL\Backup\'; -- каталог бекапа
SET @buckap_result = @buckap_folder+@from+'.bak' -- имя бэкап файла
SET @to_mdf = @buckap_folder+ @to+'.mdf' -- имя файла базы приемника
SET @to_log = @buckap_folder+ @to+'_log.ldf' -- имя лог файла базы приемника
SET @alterSingl =  N'ALTER DATABASE ' + QUOTENAME(@to) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';
SET @alterMulti =  N'ALTER DATABASE ' + QUOTENAME(@to) + ' SET MULTI_USER;';

// Создание файла бэкапа
BACKUP DATABASE @from TO DISK = @buckap_result;

// Изменение на одиночный режим базы приемника для избежания ошибки соединения с базой
EXEC sp_executesql @alterSingl;

// Восстановление базы приемника
RESTORE DATABASE @to
FROM DISK = @buckap_result
WITH REPLACE, RECOVERY,
MOVE @from TO @to_mdf,
MOVE @from_log TO @to_log;

// Изменение базы приемника на много пользовательский режим
EXEC sp_executesql @alterMulti;

// Удаление файла бекапа с диска
EXECUTE master.dbo.xp_delete_file 0, @buckap_result;

// Получение ID из msdb и удаление записи о файле бекапа из msdb
SELECT @BackupSetID = backup_set_id FROM msdb.dbo.backupset AS history WHERE database_name = @from AND recovery_model = 'Full';

IF ISNUMERIC(@BackupSetID) = 1
BEGIN
   DECLARE @BackupSetIDInt INT = CAST(@BackupSetID AS INT);
   EXEC msdb.dbo.sp_delete_backuphistory @BackupSetIDInt;
END
ELSE
BEGIN
   PRINT 'Переменная @BackupSetID не содержит числовое значение.';
END

Уменьшение данных журнала SHRINK t-sql:
ALTER DATABASE ['ИмяБазы']
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ([ИмяБазы_log], 1)
GO
ALTER DATABASE [ИмяБазы]
SET RECOVERY FULL

Поиск колонки в таблицах базы данных по имени в sql:
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name LIKE '%Fld8282%'
📰 Утилита ibcmd:
Ibcmd устанавливается вместе с кластером серверов 1С. Позволяет администрировать базу, создавать её, выгружать в ДТ или файлы. Через неё можно было выгружать и загружать *.dt уже относительно давно, но именно с версии платформы 23, появилась возможность делать это без промежуточной выгрузки в файл .dt, а это в разы ускоряет время выполнения. Данная утилита позволяет переносить базы между разными СУБД.

Скрипт для командной строки Windows для переноса базы с MS SQL и создания на Postgre SQL:
ibcmd infobase replicate --data=ВРЕМЕННОЕ_МЕСТОПОЛОЖЕНИЕ --dbms=MSSQLServer --database-server=ИМЯ_СЕРВЕРА_ИСТОЧНИКА --database-name=ИМЯ_БАЗЫ --database-user=ИМЯ_ПОЛЬЗОВАТЕЛЯ_СЕРВЕРА--database-password=ПАРОЛЬ_ПОЛЬЗОВАТЕЛЯ_СЕРВЕРА --target-dbms=PostgreSQL --target-database-server=ИМЯ_СЕРВЕРА_ПРИЕМНИКА --target-database-name=ИМЯ_БАЗЫ --target-database-user=ИМЯ_ПОЛЬЗОВАТЕЛЯ_СЕРВЕРА --target-database-password=ПАРОЛЬ_ПОЛЬЗОВАТЕЛЯ_СЕРВЕРА --target-create-database

Выполнять нужно перейдя в каталог платформы командой:
cd C:\Program Files\1cv8\8.3.23.1865\bin
Шпаргалка по основам SQL
ℹ️ Порядок выполнения SQL запроса:
0
комментарии
____________________
Copyright©, «Программист 1С в г.Минске», 09.07.2024
Перепечатка текста и фотографий разрешена при наличии прямой ссылки на источник
Яндекс.Метрика
Защищенное соединение ssl
visa
mastercard
Maestro
Яндекс деньги
Назад к содержимому