重新编译存储过程? [英] Recompile stored procs?

问题描述

有没有办法重新编译或至少检查编译"存储过程?有时我们会进行架构更改 - 添加或删除列等.并尽最大努力识别受影响的 procs 只会被我们错过的一个咬伤，当它下次运行时会呕吐.SQLServer 2k5 或 2k8.

Is there a way to re-compile or at least 'check compile' stored procedures en masse? Sometimes we'll make schema changes - add or drop a column, etc. And do our best to identify affected procs only to be bitten by one we missed, which pukes when it runs next. SQLServer 2k5 or 2k8.

推荐答案

如果您在更改表和破坏存储过程时遇到问题，请尝试 sp_depends:

If you have problems changing a table and breaking stored procedures try sp_depends:

sp_depends [ @objname = ] '<object>' 

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

并在它们破裂之前识别它们.以这种方式使用它:

and identity them before they break. Use it this way:

EXECUTE sp_depends  YourChangedTableName

此外，您还可以使用 sp_recompile:

Also, you could use sp_recompile:

EXEC sp_recompile YourChangedTable

但这只会标记相关的存储过程，以便在下次运行时重新编译.

but that only marks associated stored procedures for recompile when they are run the next time.

您可以使用管理工作室或源代码控制将所有过程的串联创建脚本生成到一个文件中，然后运行该文件.

You could use management studio or your source control to generate a concatenated create script of all procedures into a single file and then run that.

这篇关于重新编译存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！