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.


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!


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.


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?


Want to Migrate to the Cloud?

Office 365

Ready to Experience Microsoft Office 365?

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