用于更改所有存储过程中的所有表引用的 SQL 脚本

问题描述

我创建了一个包含现有表副本的新数据库，但更改了这些表的名称，是否有可以运行的 SQL 脚本(可能使用 SysObjects)来更改所有存储过程中对这些表的所有引用?

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?

推荐答案

不要依赖 INFORMATION_SCHEMA.ROUTINES 因为 ROUTINE_DEFINITION 只是 nvarchar(4000).您需要 sys.sql_modules 其中 definition 是 nvarchar(max)

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

您可以取消注释 m.definition 以列出内容，但我发现仅标识所有过程然后手动查看它们更好，因为您不想在其上运行 UPDATE 命令系统表.编写必要的程序，进行更改(搜索/替换或手动)，然后运行脚本！！！

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

这篇关于用于更改所有存储过程中的所有表引用的 SQL 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！