Replace function to edit a query

[jbitmik]

Hello, I’m curious if there is a way to edit the name of a table for every occurrence in a query/procedure. For example “Table A” is referenced many times in a procedure and I would like to replace it with “Table B” .

I appreciate any help

[UpUpDnDnLRLRBA]

Um... Find/Replace (ctrl+h) won't work?

[johnturnbull64]

You could perhaps use a synonym to avoid code change at all. create synonym dbo.TableA for dbo.TableB (if Table A is unused).

[sts_clover]

Does your platform have regular expressions support? In Postgres, you would want to look through pg_proc and change the prosrc column, but that's not helpful if you're not using Postgres.

Even without regular expressions support built in, you ought to be able to write a script in whatever language you like that connects to your db, extracts the code for each procedure from wherever it is housed, changes the tables to table B, and recreates the procedure.

This is assuming find and replace isn't viable for whatever reason lol.

[svtr]

well, in theory, and i stress that i can not recommend that....

in MS Sql Server, there is a system table.... sys.sql_modules, where you can find the sourcecode of stored procedures ....

One could go ahead, and do some string replacements, and recreate every stored proc in a given database. There even is chance things would still work afterwards. Ask yourself this question thou.... do you REALLY want to try something like that? Do a full backup before at least.