SQL script to change all table references in all stored procedures

Question

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

Thank.

Answer 1

DO NOT APPLY TO INFORMATION_SCHEMA.ROUTINES , because ROUTINE_DEFINITION- this is only nvarchar(4000). You need sys.sql_moduleswhere definition- nvarchar (max)

try any of them to find the procedure you need to change:

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%'

m.definition, , , , UPDATE . Script , ( / ), !!!

Answer 2

.

, SQL Refactor Redgate . script , .

SQL Server 2005 , .

Answer 3

SQL, , . , , .

"% TABLE_NAME%" , .

, .

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%TABLE_NAME%' AND ROUTINE_TYPE='PROCEDURE'

Answer 4

(, ), VIEW , , . , tests tests_new, :

CREATE VIEW dbo.tests
AS
SELECT * FROM dbo.tests_new

dbo.tests dbo.tests_new.

, / DB, .

Answer 5

You cannot change table references in sprocs through the system data dictionary - you will need to get a script that will create a stored procedure and change the table names in the script. If you have scripts, this is a simple search and replacement for the most part.

If you do not have scripts, you can get the text of the stored procedure scripts from sys.sql_modules or get it through SSMS.