How to update the table name in all the stored procedures?

Question

Hello All,

I need to update the table name in all the 100 stored procedures manually .
Any alternate do it

Answer 1

why do you want to update the table name?

look up apexsql if you are changing table names that reference multiple stored procs

Answer 2

Hi NeophyteSQL,

Our table name got changed where we get data from.So we need to update it in procedures

Answer 3

Does the original table still exist? If so - can it be renamed and then rename the new table to the old name?

Another option would be to use synonyms. This way, you can easily change the name and not have to make any changes to your code. For example, you could have 2 separate tables - Table1 and Table2. Create a synonym for your code to be MyTable and reference dbo.MyTable in your code.

The synonym would be: CREATE SYNONYM dbo.MyTable FOR dbo.Table1;

To switch to use Table2, drop the synonym and recreate:

DROP SYNONYM dbo.MyTable;
CREATE SYNONYM dbo.MyTable FOR dbo.Table2;

Now you code still references dbo.MyTable - but the data will come from Table2.

Instead of trying to modify your code all the time, it would be much easier using a synonym and just changing the synonym as needed.

Answer 4

You can loop the following result set

SELECT [ROUTINE_SCHEMA]
      ,[ROUTINE_NAME]
      ,[ROUTINE_DEFINITION]
  FROM [INFORMATION_SCHEMA].[ROUTINES]
WHERE [ROUTINE_TYPE] = 'PROCEDURE'
AND [INFORMATION_SCHEMA].[ROUTINES] LIKE '%[SCHEMA].[TABLE_NAME]%' -- Well-formatted
--AND [INFORMATION_SCHEMA].[ROUTINES] LIKE '%TABLE_NAME%' -- Otherwise. if there is no any other string using TABLE_NAME

and then assign the definition to the variable. Replace the old table name with the new table name in the variable and then execute that variable to re-install the SPs.

Answer 5

You should have your code under version control. In that case, you just check out the files, and find a good editor with good search/replace facilities.

Else script out the procedures from SSMS into a single file and run a fine/replace on that file.

Of course, this assumes that the old table name is significant enough and not something that also reappears in column names etc.

Note that a good editor has an option to only get matches on a whole word, to prevent that a search for "peach" also gives a hit on "peachtree".