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 dbatools Instead of RDP

Garry Bargsley shows how we can use dbatools to do some of the things which we might naturally do with Remote Desktop: So you hear this spread across the Twitterverse and Blogosphere. You should not RDP your SQL Servers to do administrative work. My nature has always been to troubleshoot issues from the server in […]

Read More

File Sizes in dbatools

Chrissy LeMaire gives us several ways to format file sizes with dbatools: Within dbatools, you may notice file sizes are pretty and human-readable. That was some C# based magic created by Microsoft PFE and creator of PSFramework, Fred Weinmann. In the background, SQL Server often gives us different types of numbers to represent file sizes. Sometimes it’s […]

Read More

Categories

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