String Replace in Stored Procedures with Powershell

###############################################
## will gather schema pertaining to tables and sps
###############################################

import-module sqlps -DisableNameChecking

write-host "Type the name of the Database Instance and press enter....." -ForegroundColor Red -BackgroundColor Blue
Write-host "If you dont know that name to run.. Select @@servername ..on your database instance"  -ForegroundColor Red -BackgroundColor Blue

$s = Read-host 


$servername = $s

##test if directory exists if it doesnt will build it 

$testpath = test-path C:\users\$env:UserName\Desktop\Sp_Schema

if($testpath -eq $True)
{

write-host "directories exist..will delete those" -ForegroundColor blue -BackgroundColor Green

    start-sleep -Seconds 2

write-host "deleting directories" -ForegroundColor blue -BackgroundColor Green

    remove-item C:\users\$env:UserName\Desktop\Sp_Schema -recurse -Force

write-host "creating new directories" -ForegroundColor blue -BackgroundColor Green

    Start-Sleep -Seconds 2

    New-Item -PATH c:\users\$env:UserName\Desktop\Sp_Schema -ItemType DIRECTORY  

    New-Item -path C:\users\$env:UserName\Desktop\Sp_Schema\Sps -ItemType DIRECTORY

}else
    {
    
    write-host "Creating Directory" -ForegroundColor Green -BackgroundColor blue 

    New-Item -PATH c:\users\$env:UserName\Desktop\Sp_Schema -ItemType DIRECTORY  

    New-Item -path C:\users\$env:UserName\Desktop\Sp_Schema\Sps -ItemType DIRECTORY


    }
  

##gather schema info and will print to screen what its doing.

write-host "scripting stored procedure schema"  -ForegroundColor Magenta 

  foreach($sp in  Get-ChildItem SQLSERVER:\SQL\$servername\Default\DATABASES\<your database name>\StoredProcedures)
    {

    write-host $sp -ForegroundColor white -BackgroundColor Green

    $files ="C:\Users\$env:UserName\Desktop\Sp_Schema\Sps\" + $($sp.Schema) + "." + $($sp.name) + ".sql"

    $sp.script() | out-file $files
    
    } 


    start-sleep -Seconds 2

Write-Host "parsing files for quoted_identifiers" -BackgroundColor Magenta -ForegroundColor white 

    start-sleep -Seconds 2


###############################################
## replace Quote_Identifier OFF and Alter Proc
###############################################


$Stored_Procedures = Get-ChildItem C:\Users\$env:UserName\Desktop\Sp_Schema\Sps

##move files to new location 

$testpath = test-path C:\users\$env:UserName\Desktop\Ident_off


if($testpath -eq $True)
{

write-host "directories exist..will delete those" -ForegroundColor blue -BackgroundColor Green

    start-sleep -Seconds 2

write-host "deleting directories" -ForegroundColor white -BackgroundColor Red

    remove-item C:\users\$env:UserName\Desktop\Ident_off -recurse -Force

write-host "creating new directories" -ForegroundColor blue -BackgroundColor Green

    Start-Sleep -Seconds 2

    New-Item -PATH C:\users\$env:UserName\Desktop\Ident_off  -ItemType DIRECTORY  -InformationAction SilentlyContinue



}else{

write-host "creating new directories" -ForegroundColor blue -BackgroundColor Green

    Start-Sleep -Seconds 2

    New-Item -PATH C:\users\$env:UserName\Desktop\Ident_off  -ItemType DIRECTORY  
}


foreach($sp in $Stored_Procedures)
    {

    
    $off = get-content C:\Users\$env:UserName\Desktop\Sp_Schema\Sps\$($Sp.name) | select-string "SET QUOTED_IDENTIFIER OFF" 

    if($off.count -eq 1)
        {
      
            write-host "found..SET QUOTED_IDENTIFIER OFF..for.. $($sp.name)" -ForegroundColor Blue -BackgroundColor Green
      
            write-host "moving..$($sp.name)..to C:\Users\$env:UserName\Desktop\Ident_off"  -ForegroundColor Green -BackgroundColor Blue
         
            copy-item -path  C:\Users\$env:UserName\Desktop\Sp_Schema\Sps\$($Sp.name) -destination C:\Users\mdspain\Desktop\Ident_off


        }
    }



##replace quoted_identifier off 

write-host "replacing quote_ident off.."

    start-sleep -Seconds 2

$ident = Get-ChildItem C:\Users\$env:UserName\Desktop\Ident_off

foreach($file in $ident)
    {
    
    
    (get-content C:\Users\$env:UserName\Desktop\Ident_off\$($file.name)) -replace "QUOTED_IDENTIFIER OFF", "QUOTED_IDENTIFIER ON" | Set-Content C:\Users\$env:UserName\Desktop\Ident_off\$($file.name)
    

    $on = get-content C:\Users\$env:UserName\Desktop\Ident_off\$($file.name)  | select-string "SET QUOTED_IDENTIFIER OFF"

        if($on.count -eq 1)
            {
                write-host $file.name
            }
    }


##add line break and Go and Alter making sure SP is first in the batch

$create = Get-ChildItem C:\Users\$env:UserName\Desktop\Ident_off

foreach($alter in $create)
    {

    ##looking for proc names with extra white space between Create Procedure 
    $count = get-content C:\Users\$env:UserName\Desktop\Ident_off\$($alter.name) | Select-string "CREATE  PROCEDURE"

    if($count.count -eq 1)
        {

    ## create line break
    $OFS = "`r`n"
    write-host found extra white space -ForegroundColor white -BackgroundColor blue 

    (get-content C:\Users\$env:UserName\Desktop\Ident_off\$($alter.name)) -replace 'CREATE  PROCEDURE', ('GO'+$OFS+'ALTER PROCEDURE') | Set-Content C:\Users\$env:UserName\Desktop\Ident_off\$($alter.name)
      
        }

    ## create line break
    $OFS = "`r`n"
  
    (get-content C:\Users\$env:UserName\Desktop\Ident_off\$($alter.name)) -replace 'CREATE PROCEDURE', ('GO'+$OFS+'ALTER PROCEDURE') | Set-Content C:\Users\$env:UserName\Desktop\Ident_off\$($alter.name)


    }
  
 write-host "Opening folder of fixed scripts" -ForegroundColor white -BackgroundColor Green 
    
    start-sleep -Seconds 2

    invoke-item C:\Users\$env:UserName\Desktop\Ident_off