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

Creating Fancy HTML Reports With Powershell

Jeffery Hicks shares several tips on creating fancy HTML reports using Powershell: Usage is pretty straightforward. You specify one or more computers and off you go. There is a default value for the resulting HTML file, but you’ll likely want to specify your own.   Because the function is generating custom HTML on the fly, I […]

Read More

Reuse Versus Learning

Fred Weinmann argues for the value in learning modules over built-it-yourself solutions for expanding knowledge: When you start with a new technology, you don’t start on a green field. You’ve got lots of luggage you carry around with you (previous experience) and a perception where you want to go (project conditions, goals). However the technology […]

Read More

Categories

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