/*
— This script generates alter statements for the user databases in an instance and takes away any hard limits.
— The rule is minimum autogrow increment 100MB, between 500-5000MB, 500MB increment and above 5000MB, 1000MB increment
*/

SET NOCOUNT ON;

SELECT
d.name AS DatabaseName,
mf.name AS FileName,
mf.type_desc AS FileType,

— Current size
CAST(CAST(mf.size AS BIGINT) * 8 / 1024 AS BIGINT) AS SizeMB,

— Current FILEGROWTH
CASE
WHEN mf.is_percent_growth = 1
THEN CAST(mf.growth AS NVARCHAR(10)) + ‘%’
ELSE CAST(CAST(mf.growth AS BIGINT) * 8 / 1024 AS NVARCHAR(20)) + ‘MB’
END AS CurrentFileGrowth,

— Current MAXSIZE
CASE
WHEN mf.max_size = -1
THEN ‘UNLIMITED’
ELSE CAST(CAST(mf.max_size AS BIGINT) * 8 / 1024 AS NVARCHAR(20)) + ‘MB’
END AS CurrentMaxSize,

— Proposed growth
CASE
WHEN CAST(mf.size AS BIGINT) * 8 / 1024 < 500 THEN 100
WHEN CAST(mf.size AS BIGINT) * 8 / 1024 BETWEEN 500 AND 5000 THEN 500
ELSE 1000
END AS NewFileGrowthMB,

— Generated SQL
'ALTER DATABASE [' + d.name + '] MODIFY FILE ' +
'( NAME = N''' + mf.name + ''', ' +
'FILEGROWTH = ' +
CAST(
CASE
WHEN CAST(mf.size AS BIGINT) * 8 / 1024 4 — user databases only
AND d.state_desc = ‘ONLINE’
AND d.is_read_only = 0
AND d.is_distributor = 0
ORDER BY
d.name,
mf.type_desc,
mf.name;