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_default = 1 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 [master];' + CHAR(13) + CHAR(10) +
'GO' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILEGROUP [' + @DbName + '_FG_CDC];' + CHAR(13) + CHAR(10) +
'GO' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILE ( NAME = N''' + @DbName + '_DF1_FG_CDC'', FILENAME = N''D:\MSSQL\Data\' + @DbName + '\' + @DbName + '_DF1_FG_CDC.ndf'' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [' + @DbName + '_FG_CDC];' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILE ( NAME = N''' + @DbName + '_DF2_FG_CDC'', FILENAME = N''E:\MSSQL\Data\' + @DbName + '\' + @DbName + '_DF2_FG_CDC.ndf'' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [' + @DbName + '_FG_CDC];' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILE ( NAME = N''' + @DbName + '_DF3_FG_CDC'', FILENAME = N''F:\MSSQL\Data\' + @DbName + '\' + @DbName + '_DF3_FG_CDC.ndf'' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [' + @DbName + '_FG_CDC];' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILE ( NAME = N''' + @DbName + '_DF4_FG_CDC'', FILENAME = N''I:\MSSQL\Data\' + @DbName + '\' + @DbName + '_DF4_FG_CDC.ndf'' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [' + @DbName + '_FG_CDC];' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILE ( NAME = N''' + @DbName + '_DF5_FG_CDC'', FILENAME = N''H:\MSSQL\Data\' + @DbName + '\' + @DbName + '_DF5_FG_CDC.ndf'' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [' + @DbName + '_FG_CDC];' + CHAR(13) + CHAR(10) +
'GO' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILEGROUP [' + @DbName + '_FG_Current];' + CHAR(13) + CHAR(10) +
'GO' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILE ( NAME = N''' + @DbName + '_DF1_FG_Current'', FILENAME = N''D:\MSSQL\Data\' + @DbName + '\' + @DbName + '_DF1_FG_Current.ndf'' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [' + @DbName + '_FG_Current];' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILE ( NAME = N''' + @DbName + '_DF2_FG_Current'', FILENAME = N''E:\MSSQL\Data\' + @DbName + '\' + @DbName + '_DF2_FG_Current.ndf'' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [' + @DbName + '_FG_Current];' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILE ( NAME = N''' + @DbName + '_DF3_FG_Current'', FILENAME = N''F:\MSSQL\Data\' + @DbName + '\' + @DbName + '_DF3_FG_Current.ndf'' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [' + @DbName + '_FG_Current];' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILE ( NAME = N''' + @DbName + '_DF4_FG_Current'', FILENAME = N''I:\MSSQL\Data\' + @DbName + '\' + @DbName + '_DF4_FG_Current.ndf'' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [' + @DbName + '_FG_Current];' + CHAR(13) + CHAR(10) +
'ALTER DATABASE [' + @DbName + '] ADD FILE ( NAME = N''' + @DbName + '_DF5_FG_Current'', FILENAME = N''H:\MSSQL\Data\' + @DbName + '\' + @DbName + '_DF5_FG_Current.ndf'' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [' + @DbName + '_FG_Current];' + CHAR(13) + CHAR(10) +
'GO' + CHAR(13) + CHAR(10) +
'USE [' + @DbName + '];' + CHAR(13) + CHAR(10) +
'GO' + CHAR(13) + CHAR(10) +
'IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N''' + @DbName + '_FG_Current'') ALTER DATABASE [' + @DbName + '] MODIFY FILEGROUP [' + @DbName + '_FG_Current] DEFAULT;' + 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;
Similar Posts: