Programmatically altering object code

Has anyone developed a way to programmatically alter the code for multiple
stored procs, functions, etc.?
For example: suppose your database contains hundreds of stored procedures
which access a certain table. Along comes a requirement to rename this
table. It's a lot of tedious work to have to individually open each proc in
QA/EM and change the code. I would like to be able to simply type something
like:
EXEC sp_alter_proc_code 'OldTable', 'NewTable'
And presto, all procs that contain SQL statements that query OldTable are
now pointing to NewTable!
Note that you cannot run an UPDATE statement against syscomment's text field
(e.g., UPDATE syscomments SET text = REPLACE(text, 'OldTable', 'NewTable')),
since this is a computed field.
One solution that I had experimented with was to capture the contents of
syscomment's text column into a variable, run REPLACE against that variable,
and EXEC it. But the problem with that is, a variable can hold only 8000
characters.
I would be interested to hear if anyone has developed a workable solution
for this.>It's a lot of tedious work to have to individually open each proc in
>QA/EM and change the code.
Script all the procs out into a single file as CREATEs (without the
delete!) Open the script in Query Analyzer. Change all the CREATE
PROCs to ALTER PROC. Change all occurances of the old table name to
the new table name - there is a REPLACE ALL feature.
Execute the script.
Roy Harvey
Beacon Falls, CT|||CadeBryant wrote:
> Has anyone developed a way to programmatically alter the code for
> multiple stored procs, functions, etc.?
> For example: suppose your database contains hundreds of stored
> procedures which access a certain table. Along comes a requirement
> to rename this table. It's a lot of tedious work to have to
> individually open each proc in QA/EM and change the code. I would
> like to be able to simply type something like:
> EXEC sp_alter_proc_code 'OldTable', 'NewTable'
> And presto, all procs that contain SQL statements that query OldTable
> are now pointing to NewTable!
> Note that you cannot run an UPDATE statement against syscomment's
> text field (e.g., UPDATE syscomments SET text = REPLACE(text,
> 'OldTable', 'NewTable')), since this is a computed field.
> One solution that I had experimented with was to capture the contents
> of syscomment's text column into a variable, run REPLACE against that
> variable, and EXEC it. But the problem with that is, a variable can
> hold only 8000 characters.
> I would be interested to hear if anyone has developed a workable
> solution for this.
If you are managing your stored procedures using version control
software outside of SQL Server, then you can simply perform a find and
replace. You really should not be managing your procedures using SQL
Server. If you are, I would recommend you extract all procedures and get
them in some form of version control software before beginning this
process.
David Gugick - SQL Server MVP
Quest Software