sql - MS-SQL:一般更改数据库的 FileGrowth 参数
问题描述
在我们的软件中,用户可以创建数据库以及连接到不是由我们的软件创建的数据库。DBMS 是 Microsoft SQL-Server。
现在我需要更新我们使用的数据库并将所有数据库的所有文件的 FileGrowth 参数设置为某个值。
我知道如何从查询中获取当前数据库文件的逻辑文件名:
SELECT file_id, name as [logical_file_name], physical_name FROM sys.database_files
一旦我知道逻辑文件名,我就知道如何设置所需的 FileGrowth 值:
ALTER DATABASE MyDB MODIFY FILE (Name='<logical file name>', FileGrowth=10%)
但我不知道如何将这些步骤组合成一个脚本。
由于有各种数据库,我无法将逻辑文件名硬编码到脚本中。对于更新过程(现在),我们只能获取数据库连接并在此连接上执行 sql 脚本,因此如果可能的话,最好使用“纯”脚本解决方案。
解决方案
以下脚本接收数据库名称作为参数并使用 2 个动态 SQL:一个用于游标以循环所选数据库的数据库文件,另一个用于应用正确的ALTER TABLE
命令,因为您不能在MODIFY FILE
.
在两种情况下EXEC
都会对 进行评论,并且有一个PRINT
代替,因此您可以在执行之前进行查看。我刚刚在我的沙盒上对其进行了测试,它按预期工作。
DECLARE @DatabaseName VARCHAR(100) = 'DBName'
DECLARE @DynamicSQLCursor VARCHAR(MAX) = '
USE ' + @DatabaseName + ';
DECLARE @FileName VARCHAR(100)
DECLARE FileCursor CURSOR FOR
SELECT S.name FROM sys.database_files AS S
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DynamicSQLAlterDatabase VARCHAR(MAX) = ''
ALTER DATABASE ' + @DatabaseName + ' MODIFY FILE (Name = '''''' + @FileName + '''''', FileGrowth = 10%)''
-- EXEC (@DynamicSQLAlterDatabase)
PRINT (@DynamicSQLAlterDatabase)
FETCH NEXT FROM FileCursor INTO @FileName
END
CLOSE FileCursor
DEALLOCATE FileCursor '
-- EXEC (@DynamicSQLCursor)
PRINT (@DynamicSQLCursor)
您可能需要检查通常的动态 SQL 警告,例如确保连接的值不会破坏 SQL 并添加错误处理。
至于如何将其应用于多个数据库,您可以创建一个 SP 并执行多次,或者在此上包装一个数据库名称 cursor/while 循环。