PowerShell: Find & Replace SQL Server Stored Procedure/View/UDF/Trigger Code En Masse Based On Custom Criteria

Update April 19, 2019: Thanks to Tim (in comments), the updated script can also do find/replace in not just stored procedures but also views, UDF’s and triggers.

Today, a need came-up where a bunch of stored procedures had to be altered based on certain criteria.

Easy way:

Obviously, the easiest way would be to script out the stored procedures to a file/editor and do Find/Replace with the UI dialog.

However, this does not work if there are lots of stored procedures and the conditions upon which to choose SP’s and replace are just as complex.

When “Easy” is not an option:

The database has 100’s of stored procedures (700+ to be somewhat precise)

Qualifying stored procedures have these characteristics

• Procedure name ends with “_del”
• Procedure has the string “exec” in the code
• Procedure has the string “sp_execute” in the code

This is what needs to be done:

• Replace CREATE PROC with ALTER PROC
• Replace SYSTEM_USER with “ORIGINAL_LOGIN()”
• Replace AS at the beginning of CREATE PROC with “WITH EXECUTE AS OWNER AS”
• Comment out some SET statements
• …in fact, there could be any number of other changes

The solution:

You do need the older SQLPS (installed with SQL Server older versions) or the newer SQLSERVER PowerShell module

Below is the actual code. I am sure you can make the necessary changes to do what you need to do.

The pseudo code goes like this:

• Setup variables
• Get all the stored procedures in the database
• Get the list of stored procedures we want to alter
• Loop through our list and locate in the full list to script ours out
• Do the replacements
• Append to output file
• Replace anything left out at the file level

Import-Module SQLServer #Could use SQLPS instead

#----------------------------------------------------------------
#Setup variables
#----------------------------------------------------------------
$instance = 'MYSQLHOST\MYINST '
$hostname = 'MYSQLHOST'
$instancename = 'MYINST '
$database = 'MYDATABASE'
$outputFile = 'C:\Temp\SQLServer\MYDATABASE\AlteredStoredProcedures.sql'

#----------------------------------------------------------------
#Get all the Stored Procedures
#----------------------------------------------------------------
$allSPs = Get-ChildItem SQLSERVER:\SQL\$hostname\$instancename\Databases\$database\StoredProcedures

#----------------------------------------------------------------
#SQL contains conditions we want to filter the stored procedures by..
#...in this case, we are looking for procedures that have "exec" or "sp_execute" in them (dynamic SQL)
#    of if the stored procedure name ends with "_del"
#----------------------------------------------------------------
$sql = "SELECT DISTINCT
               schema_name(o.schema_id) schema_nm,
               o.name AS Object_Name,
               o.type_desc
          FROM sys.sql_modules m
               INNER JOIN
               sys.objects o
                 ON m.object_id = o.object_id
        WHERE o.type_desc = 'SQL_STORED_PROCEDURE'
            ---------------------------------------
            --Change here to suit the conditions you desire
            ---------------------------------------
	        AND (m.definition Like '%exec %'
	                or m.definition Like '%sp_execute%'
                    or o.name Like '%\_del');"

$filteredSPs = Invoke-Sqlcmd -ServerInstance $instance -Database $database -Query $sql -OutputAs DataTables

#----------------------------------------------------------------
#Loop through the SP's we want to alter and alter them
#----------------------------------------------------------------
foreach($sp in $filteredSPs)
{
    $spText = ''
    $spLocated = $null
    $spLocated = ($allSPs |  ? {($_.Schema -eq "$($sp.schema_nm)") -and ($_.Name -eq "$($sp.object_name)")})

    if ($spLocated -eq $null)
    {
        Write-Warning "$($sp.schema_nm).$($sp.object_name) was not found!"
    }
    else
    {
        #This gets the script for the stored procedure as CREATE
        $spText = $spLocated.script()

        #Do case-insensitive replace
        #----------------------------------------------------------------
        #Replace CREATE with ALTER (+)
        #   Add "EXECUTE AS OWNER" (+)
        #   Replace "SYSTEM_USER" with "ORIGINAL_LOGIN()"
        #   Comment out SET statements
        #----------------------------------------------------------------
        $spText = $spText -ireplace [Regex]::Escape("`nCREATE"), "`nALTER"
        $spText = $spText -ireplace [Regex]::Escape("`nAS"), "`nWITH EXECUTE AS OWNER `nAS"
        $spText = $spText -ireplace [Regex]::Escape("SYSTEM_USER"), "ORIGINAL_LOGIN()"
        $spText = $spText -ireplace [Regex]::Escape("SET ANSI_NULLS ON"), "--SET ANSI_NULLS ON"
        $spText = $spText -ireplace [Regex]::Escape("SET QUOTED_IDENTIFIER ON"), "--SET QUOTED_IDENTIFIER ON"

        #Add a GO at the end
        $spText = $spText + "`nGO`n"

        $spText | Out-File -LiteralPath $outputFile -Force -Append    

    }

}

#For some reason, if somethings did not get replaced as intended due to lack of
# newline characters at the start for example, replace at the file level
#Replace remnant CREATE's...just replace at the file level
#
(Get-Content $outputFile -Raw).Replace("`nCREATE","`nALTER") |
Set-Content $outputFile -Force

Find & Replace In SP’s/Views/Triggers/UDF’s

Thanks to Tim (see comments section), the below script can find and replace not only in stored procedures but also views, triggers and user-defined functions. I have updated his script to include triggers. The output files will be saved to the folder specified at the top of the script.

$ServerName = 'MyHost\MyInstance' #If you have a named instance, you should put the name.
$path = 'C:\temp\dbscripts'
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
$IncludeTypes = @('StoredProcedures','Views','UserDefinedFunctions','Triggers') #object you want do backup.
$ExcludeSchemas = @('sys','Information_Schema')
$so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')
$dbs = $serverInstance.Databases #you can change this variable for a query for filter your databases.

foreach ($db in $dbs)
{
    $dbname = "$db".replace('[','').replace(']','')
    $dbpath = "$path\$dbname"
    if ( !(Test-Path $dbpath))
    {$null=New-Item -type directory -name '$dbname'-path $path -Force}

    foreach ($Type in $IncludeTypes)
    {
        foreach ($objs in $db.$Type)
        {
            If ($ExcludeSchemas -notcontains $objs.Schema )
            {
                $ObjName = "$Type.$objs".replace('[','').replace(']','').replace($objs.Schema+'.','')
                $OutFile = "$dbpath\$ObjName" + ".sql"

                Write-Verbose "$db - $Type - $ObjName - $OutFile"

                $spText = $objs.Script($so)
                $spText = $spText -ireplace [Regex]::Escape('`nCREATE '), 'ALTER '
                $spText.trim()+'GO' | Where { $_ -notmatch '^SET ANSI_NULLS ON' -and $_ -notmatch '^SET QUOTED_IDENTIFIER ON' }

                $spText | Out-File -FilePath $OutFile -Force
            }
        }

        if ($Type -eq 'Triggers')
        {
            foreach($table in $db.Tables)
            {
                foreach($trigger in $table.Triggers)
                {
                    $ObjName = ("$Type.$table." + $trigger.name).replace('[','').replace(']','')
                    $OutFile = "$dbpath\$ObjName.sql"

                    $trigText = $trigger.Script()
                    $trigText = $trigText -ireplace [Regex]::Escape('`nCREATE '), 'ALTER '

                    $trigText | Out-File -FilePath $OutFile -Force
                }
            }
        }

    }
}

Running the replaced script automatically:

There is a reason, I write to a output file first. In case there are surprises, you get a chance to catch them first before the actual stored procedures are replaced with badly replaced equivalents! Always backup first.

If you have got to a point where you are confident, you may just want to implement from the command-line after the replacements are done. You can use “sqlcmd” as shown in my blog post here:

https://sqljana.wordpress.com/2017/02/22/sql-server-run-a-script-in-command-line-mode-sqlcmd-inside-sql-server-management-studio-ssms/

Also, you can do this for views/functions etc. This is not limited to just stored procedures. The changes should be minimal to accomplish that.

Advertisement