Полезные 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-сервере (устаревшая версия)

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;