Set-FileGroupSpecification.ps1

#requires -Version 2.0
param(
    [Parameter(Mandatory=$true)]
    [string]
    $databaseRootDirectory
)
$ErrorActionPreference = 'Stop'
Set-StrictMode -Version 'Latest'

$fileGroupSpecification = "ON [PRIMARY]"
$fileGroupSpecificationPattern = [Regex]::Escape($fileGroupSpecification)

function Test-FileGroupSpecification {
    param(
        [Parameter(Mandatory=$true)]
        [string]
        $searchPath,
        
        [Parameter(Mandatory=$true)]
        [string]
        $tableName
    )
    
    $tablePathChildren = Get-ChildItem $searchPath -Recurse -Filter "$tableName*" -Exclude *table.sql 
    if ($tablePathChildren -eq $null) { return $false }
    
    foreach ($item in $tablePathChildren) {
        if ($item.PSIsContainer) { continue }
        
        $content = Get-Content $item.PSPath | Out-String   
        if ($content -match $fileGroupSpecificationPattern ) { 
            return $true
        }
    }
    
    return $false   
}

function Set-FileGroupContent {
    param(
        [Parameter(Mandatory=$true)]
        [string]
        $content,
        
        [Parameter(Mandatory=$true)]
        [string]
        $contentPath
    )
    $updatedContent = $content.Replace($fileGroupSpecification, '')
    Set-Content $contentPath -Value $updatedContent                    
}

$tableScripts = Get-ChildItem $databaseRootDirectory -Recurse -Filter *.table.sql

foreach ($tableScript in $tableScripts) {
    $content = Get-Content $tableScript.PSPath | Out-String
    $tableName = $tableScript.Name.Replace(".table.sql", "")
    
    if ($content -match $fileGroupSpecificationPattern) {
        if (Test-FileGroupSpecification $tableScript.PSParentPath $tableName) {
            Set-FileGroupContent $content $tableScript.PSPath 
            continue
        }
        
        $keysContainer = Join-Path $tableScript.PSParentPath 'Keys'
        if (Test-FileGroupSpecification $keysContainer $tableName) {
            Set-FileGroupContent $content $tableScript.PSPath
        }
    }
}