重新编译存储过程?

我将您的问题理解为“当我进行模式更改时,我希望验证所有程序是否仍然使用新模式正确执行”。例如,如果您删除了在过程中的SELECT中引用的列,那么您希望在需要更改时对其进行标记。因此,我不认为您的问题是“我希望在下次执行时重新编译该过程”,因为该作业由引擎负责,引擎将检测与任何架构更改相关的元数据版本更改,并丢弃现有缓存的执行计划。

我的第一个观察是,你在问题中所描述的通常是一个工程师的工作 您应该在部署过程中有一个QA步骤来验证新的“构建”。最好的解决方案是实现一组最小的单元测试,这些测试至少可以迭代所有存储过程并验证 处决

您的下一个最佳选择是依靠您的开发工具来跟踪这些依赖关系。这个 Visual Studio Database 2008 Database Edition 提供这种开箱即用的功能,它将负责验证您在架构中所做的任何更改。

最后,您的最后一个选择是执行与KM建议类似的操作:根据修改的对象(以及依赖于依赖对象的所有过程,等等)自动化所有过程的迭代。仅仅将过程标记为重新编译是不够的,您真正需要的是运行ALTER过程来触发对其文本的解析和模式的验证(t-SQL与您通常的语言编译/执行周期有点不同,“编译”本身只在过程实际执行时发生)。您可以从遍历 sys.sql_dependencies 查找更改对象的所有依赖项,并从中查找依赖项的“模块定义” sys.sql_modules :

with cte_dep as (
   select object_id
      from sys.sql_dependencies
    where referenced_major_id = object_id('<your altered object name>') 
    union all
    select d.object_id
    from sys.sql_dependencies d
        join cte_dep r on d.referenced_major_id = r.object_id
    )
, cte_distinct as (
    select distinct object_id
        from cte_dep)
select object_name(c.object_id)
    , c.object_id 
    , m.definition
    from cte_distinct c
    join sys.sql_modules m on c.object_id = m.object_id

然后,您可以运行相关的“模块”并重新创建它们(即,删除它们并运行“定义”中的代码)。请注意,“模块”比存储过程更通用,还包括视图、触发器、函数、规则、默认值和复制筛选器。加密的“模块”将没有可用的定义,为了绝对正确,您还必须考虑中捕获的各种设置 sys.sql\u模块 (ansi NULL、模式绑定、execute as子句等)。

如果使用的是动态SQL,则无法验证。它不会被捕获 sys.sql\u依赖项 ,也不会通过“重新创建”模块进行验证。

总的来说,我认为最好的选择是实现单元测试验证。

如果在更改表和中断存储过程时遇到问题,请尝试 视情况而定 :

sp_depends [ @objname = ] '<object>' 

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name.
        object_name
}

并在他们崩溃之前确认他们的身份。这样使用它:

EXECUTE sp_depends  YourChangedTableName

sp_重新编译 :

EXEC sp_recompile YourChangedTable

您可以使用ManagementStudio或源代码管理将所有过程的连接创建脚本生成到单个文件中,然后运行该脚本。

我知道你的意思,在很多情况下,我都意识到你的需要。你可以看看 sp_refreshsqlmodule .

您可以使用DBCC FREEPROCCACHE

http://msdn.microsoft.com/en-us/library/ms174283.aspx

在从中获取列表后,只需遍历它们 sysobjects ,然后跑 sp_recompile :

下面是一个显示示例脚本的链接:
http://database.ittoolbox.com/groups/technical-functional/sql-server-l/recompile-all-stored-procedures-2764478