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

Данные > Примеры кода 1С > 1С 8.3 Приложение, система, интерфейс

ПРИМЕРЫ КОДА 1С

Перейти в раздел примеры кода 1С 8.3:

SQL или язык структурированных запросов это декларативный язык программирования, который используют для создания, обработки и хранения данных в реляционных БД. Система управления базами данных или SQL Server обеспечивает хранение баз данных и доступ к ним из программ 1С. Подключение SQL необходимо при работе в 1С в клиент-серверном режиме, это позволяет оптимизировать работу большого количества пользователей с большим объёмом информации, за счет переноса ресурсоёмких операций на сервер. Используется разработчиками 1С для администрирования информационных баз, написания запросов, интеграций.

📰 Набор скриптов для знакомства с MS SQL Server:

Cбросить пароль в базе 1C:

-- Переименовать таблицу v8users

EXEC sp_rename 'v8users', 'v8users_tmp'

-- Переименовать users.usr в users.usr_tmp

UPDATE Params

SET FileName = 'users.usr_tmp'

WHERE FileName = 'users.usr'

-- Создать пустую таблицу 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

DBCC SHRINKFILE ([ИмяБазы_log], 1)

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