Полезные SQL скрипты для 1С

▶ Создание учетки на sql-сервере и добавление в базы данных

-- =============================================
-- Параметры (изменяйте только здесь)
-- =============================================
DECLARE @LoginName  NVARCHAR(128) = N'login_name';
DECLARE @Password   NVARCHAR(128) = N'password';
DECLARE @Databases  NVARCHAR(MAX) = N'base1,base2,base3,base4';  -- базы через запятую

-- =============================================
-- Основная логика (не меняйте)
-- =============================================

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

-- 1. Создание логина на уровне сервера
SET @SQL = N'
USE master;
CREATE LOGIN ' + QUOTENAME(@LoginName) + N' 
    WITH PASSWORD = ' + QUOTENAME(@Password, '''') + N';';

EXEC sp_executesql @SQL;

PRINT 'Логин ' + @LoginName + ' успешно создан.';

-- 2. Предоставление права ALTER TRACE
SET @SQL = N'GRANT ALTER TRACE TO ' + QUOTENAME(@LoginName) + N';';
EXEC sp_executesql @SQL;

PRINT 'Право ALTER TRACE выдано.';

-- 3. Обработка каждой базы данных
DECLARE @DBName NVARCHAR(128);
DECLARE @Cur CURSOR;

SET @Cur = CURSOR LOCAL FAST_FORWARD FOR
    SELECT TRIM(value) 
    FROM STRING_SPLIT(@Databases, ',');

OPEN @Cur;
FETCH NEXT FROM @Cur INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF DB_ID(@DBName) IS NOT NULL
    BEGIN
        SET @SQL = N'
USE ' + QUOTENAME(@DBName) + N';
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QUOTENAME(@LoginName, '''') + N')
    CREATE USER ' + QUOTENAME(@LoginName) + N' FOR LOGIN ' + QUOTENAME(@LoginName) + N';

ALTER ROLE db_datareader ADD MEMBER ' + QUOTENAME(@LoginName) + N';';

        EXEC sp_executesql @SQL;
        
        PRINT 'Пользователь создан и добавлен в db_datareader в базе: ' + @DBName;
    END
    ELSE
    BEGIN
        PRINT 'Предупреждение: База ' + @DBName + ' не найдена!';
    END

    FETCH NEXT FROM @Cur INTO @DBName;
END

CLOSE @Cur;
DEALLOCATE @Cur;

PRINT 'Скрипт выполнен успешно.';

▶ Удаление учетки из баз и с sql-сервера

/* ============================================================
   Удаление пользователя из всех пользовательских БД
   и последующее удаление серверного логина

   SQL Server 2019 / 2022
   ============================================================ */

SET NOCOUNT ON;

DECLARE @LoginName sysname = N'UserName';  -- <<< УКАЖИТЕ ИМЯ ЛОГИНА

DECLARE @DatabaseName sysname;
DECLARE @SQL nvarchar(max);

IF NOT EXISTS
(
    SELECT 1
    FROM sys.server_principals
    WHERE name = @LoginName
)
BEGIN
    PRINT N'Логин [' + @LoginName + N'] не найден на сервере.';
    RETURN;
END;


/* ============================================================
   1. Удаляем пользователя из всех пользовательских БД
   ============================================================ */

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.databases
WHERE database_id > 4              -- только пользовательские БД
  AND state_desc = N'ONLINE'
  AND is_read_only = 0;

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'
USE ' + QUOTENAME(@DatabaseName) + N';

IF EXISTS
(
    SELECT 1
    FROM sys.database_principals
    WHERE name = @LoginName
)
BEGIN
    DROP USER ' + QUOTENAME(@LoginName) + N';
END;
';

    BEGIN TRY
        EXEC sys.sp_executesql
            @SQL,
            N'@LoginName sysname',
            @LoginName = @LoginName;
    END TRY
    BEGIN CATCH
        PRINT N'Не удалось обработать базу [' + @DatabaseName + N']: '
            + ERROR_MESSAGE();
    END CATCH;

    FETCH NEXT FROM db_cursor INTO @DatabaseName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;


/* ============================================================
   2. Удаляем серверный логин
   ============================================================ */

IF EXISTS
(
    SELECT 1
    FROM sys.server_principals
    WHERE name = @LoginName
)
BEGIN
    BEGIN TRY
        SET @SQL = N'DROP LOGIN ' + QUOTENAME(@LoginName) + N';';
        EXEC sys.sp_executesql @SQL;

        PRINT N'Логин [' + @LoginName + N'] успешно удалён.';
    END TRY
    BEGIN CATCH
        PRINT N'Не удалось удалить логин [' + @LoginName + N']: '
            + ERROR_MESSAGE();
    END CATCH;
END;

▶ Создание пользователя на sql-сервере (устаревшая версия)

USE master;
GO

-- Создание логина для сервера
CREATE LOGIN login_name WITH PASSWORD = 'password';
GO

-- Предоставление разрешения ALTER TRACE (уровень сервера)
GRANT ALTER TRACE TO login_name;
GO

USE torg_sm;
GO

-- Создание пользователя в базе данных и привязка к логину
CREATE USER login_name FOR LOGIN login_name;
GO

-- Выдача прав на чтение в целевой базе данных
ALTER ROLE db_datareader ADD MEMBER login_name;
GO

USE torg_cb;
GO

-- Создание пользователя в базе данных и привязка к логину
CREATE USER login_name FOR LOGIN login_name;
GO

-- Выдача прав на чтение в целевой базе данных
ALTER ROLE db_datareader ADD MEMBER login_name;
GO

▶ Удаление пользователя с SQL-сервера и из всех баз:

DECLARE @LoginName NVARCHAR(128) = 'login_name';  -- укажите имя пользователя/логина
DECLARE @Sql NVARCHAR(MAX);

-- Курсор по всем пользовательским базам (исключая системные)
DECLARE db_cursor CURSOR FOR
    SELECT name FROM sys.databases
    WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'distribution')
      AND state = 0;   -- только онлайн-базы

OPEN db_cursor;
DECLARE @DBName NVARCHAR(128);
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = N'
        USE [' + @DBName + N'];
        BEGIN TRY
            IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @LoginName + N''' AND type IN (''S'', ''U'', ''G''))
            BEGIN
                DROP USER [' + @LoginName + N'];
                PRINT ''Пользователь "'' + ''' + @LoginName + N''' + ''" удалён из базы "'' + DB_NAME() + ''"'';
            END
            ELSE
            BEGIN
                PRINT ''Пользователь "'' + ''' + @LoginName + N''' + ''" не найден в базе "'' + DB_NAME() + ''"'';
            END
        END TRY
        BEGIN CATCH
            PRINT ''Ошибка при удалении пользователя из базы "'' + DB_NAME() + ''": '' + ERROR_MESSAGE();
        END CATCH
    ';
    EXEC sp_executesql @Sql;
    
    FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- Теперь удаляем серверный логин, если он существует
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @LoginName AND type IN ('S', 'U', 'G'))
BEGIN
    SET @Sql = N'DROP LOGIN [' + @LoginName + N'];';
    EXEC sp_executesql @Sql;
    PRINT 'Логин "' + @LoginName + '" удалён с сервера.';
END
ELSE
BEGIN
    PRINT 'Логин "' + @LoginName + '" не существовал на сервере, но пользователи (если были) удалены из баз.';
END

▶ Восстановление базы запросом:

USE master;
GO

-- 1. Посмотрим имена логических файлов для базы в бекапе, нужно использовать в 3 пункте
RESTORE FILELISTONLY FROM DISK = 'd:\backup_sql\upp_backup_2026_05_22_000109_0817520.bak';

-- 2. Если база существует, принудительно закрываем все сессии и удаляем её
IF DB_ID('upp_test') IS NOT NULL
BEGIN
    ALTER DATABASE upp_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE upp_test;
    PRINT 'База upp_test удалена.';
END
GO

-- 3. Восстановление из бэкапа
RESTORE DATABASE upp_test
FROM DISK = 'd:\backup_sql\upp_backup_2026_05_22_000109_0817520.bak'
WITH MOVE 'upp'      TO 'd:\MSSQL\Data\upp_test.mdf',
     MOVE 'upp_log'  TO 'd:\MSSQL\Data\upp_test_log.ldf',
     REPLACE;         -- RECOVERY можно не указывать (по умолчанию)
GO

▶ Просмотр размера всех баз на sql-сервере:

SELECT 
    d.name AS [Database Name],
    CAST(SUM(mf.size) * 8.0 / 1024 AS DECIMAL(18,2)) AS [Size MB],
    CAST(SUM(mf.size) * 8.0 / 1024 / 1024 AS DECIMAL(18,2)) AS [Size GB],
    CAST(SUM(CASE WHEN mf.type = 0 THEN mf.size ELSE 0 END) * 8.0 / 1024 AS DECIMAL(18,2)) AS [Data MB],
    CAST(SUM(CASE WHEN mf.type = 1 THEN mf.size ELSE 0 END) * 8.0 / 1024 AS DECIMAL(18,2)) AS [Log MB]
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.database_id > 4                    -- исключаем системные БД
  AND d.state = 0                             -- только ONLINE
GROUP BY d.name
ORDER BY [Size MB] DESC;

▶ Просмотр размера таблиц базы:

USE base;

WITH TableSizes AS (
    SELECT 
        s.name AS SchemaName,
        t.name AS TableName,
        SUM(p.rows) AS [RowCount],
        -- Размер данных (heap + clustered index)
        SUM(CASE WHEN i.index_id IN (0, 1) THEN a.used_pages ELSE 0 END) * 8.0 / 1024 AS DataMB,
        -- Размер некластерных индексов
        SUM(CASE WHEN i.index_id > 1 THEN a.used_pages ELSE 0 END) * 8.0 / 1024 AS IndexMB,
        -- Общий размер (данные + индексы)
        SUM(a.used_pages) * 8.0 / 1024 AS TotalMB
    FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    WHERE t.is_ms_shipped = 0
      AND t.name NOT LIKE 'dt%'          -- исключаем служебные таблицы диаграмм
    GROUP BY s.name, t.name
)
SELECT 
    SchemaName,
    TableName,
    [RowCount],
    CAST(DataMB AS DECIMAL(18,2)) AS DataMB,
    CAST(IndexMB AS DECIMAL(18,2)) AS IndexMB,
    CAST(TotalMB AS DECIMAL(18,2)) AS TotalMB,
    CAST(TotalMB * 100.0 / SUM(TotalMB) OVER() AS DECIMAL(5,2)) AS PercentOfTotal
FROM TableSizes
ORDER BY TotalMB DESC;

▶ Очистка файла лога всех пользовательских баз:

DECLARE @DBName sysname;
DECLARE @SQL nvarchar(max);

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.databases
WHERE database_id > 4              -- только пользовательские БД
  AND state_desc = 'ONLINE'
  AND recovery_model_desc = 'SIMPLE';

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Обработка базы: ' + QUOTENAME(@DBName);

    SET @SQL = N'
USE ' + QUOTENAME(@DBName) + N';

DECLARE @LogFile sysname;

DECLARE log_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.database_files
WHERE type_desc = ''LOG'';

OPEN log_cursor;

FETCH NEXT FROM log_cursor INTO @LogFile;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ''  Shrink log: '' + QUOTENAME(@LogFile);

    DBCC SHRINKFILE (@LogFile, 0) WITH NO_INFOMSGS;

    FETCH NEXT FROM log_cursor INTO @LogFile;
END;

CLOSE log_cursor;
DEALLOCATE log_cursor;
';

    BEGIN TRY
        EXEC sys.sp_executesql @SQL;
    END TRY
    BEGIN CATCH
        PRINT 'ОШИБКА в базе ' + QUOTENAME(@DBName) +
              ': ' + ERROR_MESSAGE();
    END CATCH;

    FETCH NEXT FROM db_cursor INTO @DBName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

▶ Просмотр распределения нагрузки на CPU по базам:

IF OBJECT_ID('tempdb..#s1') IS NOT NULL DROP TABLE #s1;

SELECT s.session_id, s.login_time, s.database_id,
       s.cpu_time + ISNULL(r.cpu_time, 0) AS cpu
INTO #s1
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
WHERE s.is_user_process = 1;

WAITFOR DELAY '00:01:00';

WITH d AS (
    SELECT s.session_id, s.host_process_id, s.database_id,
           s.cpu_time + ISNULL(r.cpu_time, 0) - ISNULL(p.cpu, 0) AS cpu_ms
    FROM sys.dm_exec_sessions s
    LEFT JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
    LEFT JOIN #s1 p ON p.session_id = s.session_id AND p.login_time = s.login_time
    WHERE s.is_user_process = 1
)
SELECT DB_NAME(database_id) AS db,
       COUNT(*) AS connections,
       SUM(cpu_ms) / 1000 AS cpu_sec,
       CAST(SUM(cpu_ms) / 60000.0 AS decimal(6,2)) AS avg_cores
FROM d
WHERE cpu_ms > 0
GROUP BY database_id
ORDER BY cpu_sec DESC;