SQL script to change all table references in all stored procedures

Question

I have created a new database with copies of existing tables but changed the names of these tables, is there a SQL script that I can run (maybe using SysObjects) to change all references to these tables in all stored procedures?

Answer 1

DO NOT RELY ON INFORMATION_SCHEMA.ROUTINES because ROUTINE_DEFINITION is only nvarchar(4000). You need to sys.sql_modules where definition is nvarchar(max)

try any of these to find the procedure that you need to modify:

SELECT DISTINCT
    LEFT(s.name+'.'+o.name, 100) AS Object_Name,o.type_desc --, m.definition
    FROM sys.sql_modules        m
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
        INNER JOIN sys.schemas  s ON o.schema_id=s.schema_id
    WHERE m.definition Like '%'+@SearchValue+'%'
    ORDER BY 1

SELECT
    OBJECT_SCHEMA_NAME(m.object_id)+'.'+OBJECT_NAME(m.object_id) --, m.definition
    FROM sys.sql_modules  m
    WHERE m.definition like '%whatever%'

SELECT
    OBJECT_SCHEMA_NAME(m.object_id)+'.'+OBJECT_NAME(m.object_id), o.type_desc
        --,m.definition
    FROM sys.sql_modules        m
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition like '%whatever%'

you can uncomment m.definition to list out the content, but I find it better to just ID all the procedures and then review them manually, because you don't want to run UPDATE commands on the system tables. Script out the necessary procedures, make the changes (search/replace or manually), and then run the scripts!!!