String Replace in Stored Procedures with Powershell

convergiafaq30, 2018-11-13 (first published: 2018-11-06)

A customer had an issue where quoted_identifiers was being turned off for random SPs.  My task wasn't to find out why that was happening but to find a way to script out the SPs and replace the proper text and alter the procedures as it was causing an outage.  This script is intended to be run by the customer and on a box with the sqlps module.   Call the script from powershell proper or open the script in Powershell ISE and execute.   The script will ask you for a DB instance name.  Progress wil be written to the screen.

Hope you find this useful. 

Michael D'Spain 

http://thesurfingdba.weebly.com

    ###############################################
## 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

Rate

5 (1)

Share

Share

Rate

5 (1)

Related content