Как сгенерировать скрипт AUTOGROW включить автоматического увеличения всех файлов

автор: автор автор
3 декабря, 2024
DECLARE @Sql NVARCHAR(MAX);
DECLARE @DbName NVARCHAR(128);
 
-- Удаление временных таблиц, если они уже существуют
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL DROP TABLE tempdb..#DatabaseFG;
IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL DROP TABLE tempdb..#SysFiles;
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL DROP TABLE tempdb..#DatabaseList;
 
-- Создание временных таблиц
CREATE TABLE #DatabaseFG (
    DatabaseName NVARCHAR(128),
    FG_name NVARCHAR(128),
    data_space_id INT,
    type NVARCHAR(128),
    is_default INT,
    is_autogrow_all_files INT
);
 
CREATE TABLE #SysFiles (
    DatabaseName NVARCHAR(128),
    FileName NVARCHAR(128),
    FileSizeMB DECIMAL(18, 2),
    SpaceUsedMB DECIMAL(18, 2),
    FreeSpaceMB DECIMAL(18, 2)
);
 
CREATE TABLE #DatabaseList (
    DatabaseName NVARCHAR(128)
);
 
-- Курсор для обхода баз данных
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE';
 
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DbName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Выполняем запрос для текущей базы
    SET @Sql = 'USE [' + @DbName + ']; ' +
               'INSERT INTO #DatabaseFG (DatabaseName, FG_name, data_space_id, type, is_default, is_autogrow_all_files) ' +
               'SELECT ''' + @DbName + ''' as DatabaseName, name as FG_name, data_space_id, type, is_default, is_autogrow_all_files FROM sys.filegroups; ' +
               'INSERT INTO #SysFiles (DatabaseName, FileName, FileSizeMB, SpaceUsedMB, FreeSpaceMB) ' +
               'SELECT ''' + @DbName + ''' AS DatabaseName, a.name AS FileName, ' +
               'CONVERT(DECIMAL(18, 2), ROUND(a.size / 128.000, 2)) AS FileSizeMB, ' +
               'CONVERT(DECIMAL(18, 2), ROUND(FILEPROPERTY(a.name, ''SpaceUsed'') / 128.000, 2)) AS SpaceUsedMB, ' +
               'CONVERT(DECIMAL(18, 2), ROUND((a.size - FILEPROPERTY(a.name, ''SpaceUsed'')) / 128.000, 2)) AS FreeSpaceMB ' +
               'FROM dbo.sysfiles a;';
    EXEC sp_executesql @Sql;
 
    -- Добавляем в список обработанные базы
    INSERT INTO #DatabaseList (DatabaseName)
    SELECT DISTINCT DatabaseName FROM #DatabaseFG WHERE is_autogrow_all_files = 0 AND FG_name = 'PRIMARY';
 
    FETCH NEXT FROM db_cursor INTO @DbName;
     
END;
 
CLOSE db_cursor;
DEALLOCATE db_cursor;
 
-- Переменная для накопления итогового скрипта
DECLARE @FinalScript NVARCHAR(MAX) = '';
 
-- Курсор для генерации скриптов файловых групп
DECLARE db_list_cursor CURSOR FOR
SELECT DISTINCT DatabaseName FROM #DatabaseList;
 
OPEN db_list_cursor;
FETCH NEXT FROM db_list_cursor INTO @DbName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql =

 
    'USE [' + @DbName + '];' + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10) +
    'DECLARE @autogrow BIT;' + CHAR(13) + CHAR(10) +
    'SELECT @autogrow = CONVERT(BIT, is_autogrow_all_files) FROM sys.filegroups WHERE name = N''PRIMARY'';' + CHAR(13) + CHAR(10) +
    'IF (@autogrow = 0)' + CHAR(13) + CHAR(10) +
    '    ALTER DATABASE [' + @DbName + '] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES;' + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10) +

    'USE [' + @DbName + '];' + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10) +
    'DECLARE @autogrow BIT;' + CHAR(13) + CHAR(10) +
    'SELECT @autogrow = CONVERT(BIT, is_autogrow_all_files) FROM sys.filegroups WHERE name = N''' + @DbName + '_FG_CDC'';' + CHAR(13) + CHAR(10) +
    'IF (@autogrow = 0)' + CHAR(13) + CHAR(10) +
    '    ALTER DATABASE [' + @DbName + '] MODIFY FILEGROUP [' + @DbName + '_FG_CDC] AUTOGROW_ALL_FILES;' + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10) +

    'USE [' + @DbName + '];' + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10) +
    'DECLARE @autogrow BIT;' + CHAR(13) + CHAR(10) +
    'SELECT @autogrow = CONVERT(BIT, is_autogrow_all_files) FROM sys.filegroups WHERE name = N''' + @DbName + '_FG_Current'';' + CHAR(13) + CHAR(10) +
    'IF (@autogrow = 0)' + CHAR(13) + CHAR(10) +
    '    ALTER DATABASE [' + @DbName + '] MODIFY FILEGROUP [' + @DbName + '_FG_Current] AUTOGROW_ALL_FILES;' + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10) +

    'USE [master];' + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10) +
    'DECLARE @autogrow BIT;' + CHAR(13) + CHAR(10) +
    'SELECT @autogrow = CONVERT(BIT, is_autogrow_all_files) FROM sys.filegroups WHERE name = N''' + @DbName + '_FG_CDC'';' + CHAR(13) + CHAR(10) +
    'IF (@autogrow = 0)' + CHAR(13) + CHAR(10) +
    '    ALTER DATABASE [' + @DbName + '] MODIFY FILEGROUP [' + @DbName + '_FG_CDC] AUTOGROW_ALL_FILES;' + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10) +

    'DECLARE @autogrow BIT;' + CHAR(13) + CHAR(10) +
    'SELECT @autogrow = CONVERT(BIT, is_autogrow_all_files) FROM sys.filegroups WHERE name = N''' + @DbName + '_FG_Current'';' + CHAR(13) + CHAR(10) +
    'IF (@autogrow = 0)' + CHAR(13) + CHAR(10) +
    '    ALTER DATABASE [' + @DbName + '] MODIFY FILEGROUP [' + @DbName + '_FG_Current] AUTOGROW_ALL_FILES;' + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10) +

    'DECLARE @autogrow BIT;' + CHAR(13) + CHAR(10) +
    'SELECT @autogrow = CONVERT(BIT, is_autogrow_all_files) FROM sys.filegroups WHERE name = N''PRIMARY'';' + CHAR(13) + CHAR(10) +
    'IF (@autogrow = 0)' + CHAR(13) + CHAR(10) +
    '    ALTER DATABASE [' + @DbName + '] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES;' + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10);



 
    SET @FinalScript = @FinalScript + @Sql;
    PRINT @Sql;
    FETCH NEXT FROM db_list_cursor INTO @DbName;
END;
 
CLOSE db_list_cursor;
DEALLOCATE db_list_cursor;
 
-- Вывод итогового скрипта
--PRINT @FinalScript;
select @FinalScript;
-- Удаление временных таблиц
DROP TABLE #DatabaseFG;
DROP TABLE #SysFiles;
DROP TABLE #DatabaseList;
Как сгенерировать скрипт AUTOGROW включить автоматического увеличения всех файлов

Similar Posts:

Добавить комментарий Отменить ответ
