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