I Need To Make A Change With My IT Support! Call (225) 706-8414

Microsoft SQL Server – Search and Replace for Stored Procedures from the Command Line

You have a nightmare on your hands. You have several hundred MS-SQL stored procedures and they all have a now-incorrect hard-coded value that needs to be changed. First, ask yourself why you are hard-coding this stuff. Second, once you realize it was the previous guy and you are safe, roll up your sleeves and.. what? Manually updated those T-SQL stored procedures will be a nightmare. Unless of course you can automate the process.

Solution

Microsoft includes sqlcmd.exe, which lets you do just about anything and everything from the command-line. Mix that, a little research, a DOS batch script, and voila, you have a way to automate this.

The Search and Replace

Refer to sql_sp_searchreplace.cmd for a DOS batch command that does a search and replace for basic text (no weird special characters, this is DOS batch scripting after all) in a T-SQL stored procedure. You run the batch file like so:

C:> sql_sp_searchreplace.cmd sql-1 KPI SASOppTest AMTS AMTS-23

This runs the script against the SQL Server sql-1, the database KPI, the stored procedure SASOppTest, the old text “AMTS”, and the new text “AMTS-23”.

When run, the batch file will download the stored procedure, update the text, and upload the stored procedure back. Also, since we’re being smart, we make a backup of the original stored procedure with a date/time stamp on the filename.. just in case!

sql_sp_searchreplace.cmd

Listing the T-SQL Stored Procedures

But, wait, we still have a problem. We need to list all stored procedures. This can be using sqlcmd again, as shown in sql_list_all_sp.cmd. We simply list out entries of type P in sysobjects for the given database.

sql_list_all_sp.cmd

Running the Job

Okay, now we have all of the tools we need. Let’s run this!

C:>for /F %a in (‘sql_list_all_sp.cmd SQL-1 KPI’) do (

call sql_sp_searchreplace.cmd SQL-1 KPI %a AMTS AMTS-23

)


photo credit: therefromhere via photo pin cc

 

Concerned About Cyber Attacks?

CLICK HERE >

Want to Migrate to the Cloud?

CLICK HERE >
Office 365

Ready to Experience Microsoft Office 365?

Want the latest IT news directly in your inbox? Subscribe now!