Alter database name and filegrowth with variable in SQL Server

Question

I want to change the filegrowth of my database via a SQL script. For the database name I want to use a variable.

Can anyone help me with this script for SQL Server?

DECLARE @db_name nvarchar(200);

SELECT @db_name = name 
FROM sys.database_files 
WHERE type = 0;

PRINT @db_name;

ALTER DATABASE [@db_name] 
  MODIFY FILE ( NAME = @db_name, FILEGROWTH = 100MB)

Thanks....

Answer 1

You cannot do this directly - SQL Server doesn't allow variables for table and column names in most its commands.

You'll need to use a dynamic SQL query - creating the query as a string and then executing it:

DECLARE @db_name nvarchar(200);

SELECT @db_name = name 
FROM sys.database_files 
WHERE type = 0;

PRINT @db_name;

DECLARE @sqlquery NVARCHAR(500);

SET @sqlquery = N'ALTER DATABASE [' + @db_name + 
                N'] MODIFY FILE ( NAME = ' + @db_name +    
                N', FILEGROWTH = 100MB);';

PRINT @sqlquery

EXEC sp_executesql @sqlquery