###########################################################################################
# Scenario: Unrestrict Growth size on all Database Files that has AutoGrowth set as None
# The following SQL 2008 Powershell sample code enumerates all  user database data files and sets Autogrowth by 10%
#
#  How to use this powershell script:
#    - Launch SQL Server Management  Studio ( Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio)
#    - Connect to SQL Server instance, Right click on the server node in Object Explorer and click on "Start Power Shell" menu
#    - Copy the following powershell script and paste it in "SQL Server PowerShell command window
###########################################################################################

# Gets the list of files, Growth types for all databases
function GetFileInfoList()
{
        $fileInfoList = @()
        #get list of databases on this sql instance
        $userDatabaseList = dir Databases

        #enumerate list of databases
        foreach($db in $userDatabaseList)
        {
                $fileGroupPSPath = "Databases\" + $db.Name + "\FileGroups"
                pushd $fileGroupPSPath
               
                # Get list of FileGroups for this database
                $fileGroupCollection = (get-item .).Collection
               
                foreach($fileGroup in $fileGroupCollection)     
                {
                        $filesPSPath = ".\" + $fileGroup.Name + "\Files"
                        pushd $filesPSPath
                       
                        # get list of files
                        $fileList = (get-item .).Collection
                       
                        foreach($file in $fileList )   
                        {
                                $fileInfoList += $file
                        }
                        popd
                }
                popd
        }
        $fileInfoList;
}


# Set GrowthType
function SetFileGrowthType($fileList, $GrowthType = [Microsoft.SqlServer.Management.Smo.FileGrowthType]::Percent , $Growth=10)
{
        $fileListWithSpecificGrowthType = $fileList   | where-object { $_.GrowthType -eq [Microsoft.SqlServer.Management.Smo.FileGrowthType]::None }

        # enumerate files with no growth type
        foreach($fileWithNoGrowthType in $fileListWithSpecificGrowthType  )     
        {
                if( $fileWithNoGrowthType -ne $null)
                {
                        $fileWithNoGrowthType.GrowthType = $GrowthType
                        $fileWithNoGrowthType.Growth = $Growth
               
                        write-host "Updating file:" ,  $fileWithNoGrowthType.Name
                        write-host "GrowthType:" ,  $GrowthType
                        write-host "Growth:" ,  $Growth
                       
                        $fileWithNoGrowthType.Alter()
                }
        }
}

$fileList = GetFileInfoList

$fileList  | select-object Name, GrowthType,Growth

SetFileGrowthType($fileList )