SQL script to change all table references in all stored procedures

I have created a new database wi开发者_JS百科th 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?

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!!!

No.

I believe SQL Refactor from Redgate has this functionality. Otherwise you could script out all objects and either manually or via code do a search and replace.

SQL Server 2005 also has support for synonyms that might be of some help.

Here is a chunk of SQL that you could use to retrieve the definition of stored procedures that match a certain search criteria. You could simply change it to do a search and replace like Martin had suggested.

Simply change '%TABLE_NAME%' to your search criteria or the table name you would like to change.

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

If it's short term (like a testing database), the easier solution may be to make a VIEW for each table that you changed, using the old name. For instance, if you changed the table tests to tests_new you can do:

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

All your procedures will that reference dbo.tests will actually look at the data in dbo.tests_new.

This is a very very bad idea if this will be a permanent/production DB, as it just adds a layer of obfuscation to your structure and will make it a nightmare to maintain.

You can't change the table references in sprocs through the system data dictionary - you will have to get the script that creates the stored procedure and change the table names in the script. If you have the scripts this is a simple search and replace for the most part.

If you don't have the scripts you can get the text of the stored procedure scripts from sys.sql_modules, or retrieve it through SSMS.