CODEWITHSUNDEEP
sqlsql-server
Nostromo
Nostromo

Reputation: 1264

MS-SQL: Changing the FileGrowth parameters of a database generically

In our software the user can create databases as well as connect to databases that were not created by our software. The DBMS is Microsoft SQL-Server.

Now I need to update the databases that we use and set the FileGrowth parameter of all the files of all the databases to a certain value.

I know how to get the logical file names of the files of the current database from a query:

SELECT file_id, name as [logical_file_name], physical_name FROM sys.database_files

And I know how to set the desired FileGrowth value, once I know the logical file name:

ALTER DATABASE MyDB MODIFY FILE (Name='<logical file name>', FileGrowth=10%)

But I don't know how to combine these to steps into one script.

Since there are various databases I can't hard code the logical file names into the script. And for the update process (right now) we only have the possibility to get the connection of a database and execute sql scripts on this connection, so a "pure" script solution would be best, if that's possible.

Upvotes: 1

Views: 583

Answers (1)

EzLo
EzLo

Reputation: 14189

The following script receives a database name as parameter and uses 2 dynamic SQL: one for a cursor to cycle database files of chosen database and another to apply the proper ALTER TABLE command, since you can't use a variable for the file name on MODIFY FILE.

The EXEC is commented on both occasions and there's a PRINT instead, so you can review before executing. I've just tested it on my sandbox and it's working as expected.

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)

You might want to check for the usual dynamic SQL caveats like making sure the values being concatenated won't break the SQL and also add error handling.

As for how to apply this to several databases, you can create an SP and execute it several times, or wrap a database name cursor / while loop over this.

Upvotes: 1

Related Questions