How to change schema name of a table in all stored procedures

Question

I know how to change the schema of a table in SQL server 2005:

ALTER SCHEMA NewSchama TRANSFER dbo.Table1

But how can i check and/or alter stored procedures that use the old schema name?

Sorry: I mean: There are stored procedures that have the old schema name of the table in the sql of the stored procedure... How can i edit all the stored procedures that have the dbo.Table1 in the body of the procedure...

Answer 1

• Use Tasks>Generate Scripts in SSMS to provide a series of Create Proc scripts.
• Use Find & Replace (Alt - H) to change 'Create ' to 'Alter '
• Use F & R to change 'dbo.Table1' to 'dbo.Table2'
• Then Execute (F5) to modify all the affected SPs.

Simple but effective.