Find And Replace Stored Procedure Code

Jana Sattainathan has to find and replace a lot of code:

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

Read on to see how Jana did it.

Related Posts

Using Show-Command in Powershell

Kenneth Fisher walks through the Show-Command cmdlet in Powershell: Years ago I blogged about how I like to use the SSMS scripting feature to learn how to do things. Well now I’m starting to learn Powershell and it turns out there is a GUI here as well that will help me learn to script. At least […]

Read More

dbatools and Linux

Chrissy LeMaire takes us through dbatools support on Linux: As a long-time Linux user and open-source advocate, I was beyond excited when PowerShell and SQL Server came to Linux. A few of the decisions I made about dbatools were actually inspired by Linux. For instance, when dbatools was initially released, it was GNU GPL licensed, which is the same license as […]

Read More

Categories

February 2018
MTWTFSS
« Jan Mar »
 1234
567891011
12131415161718
19202122232425
262728