SQL Server-避免延迟编译 [英] Sql Server - Avoid deferred compilation

问题描述

我有一个应用程序可以同时查询和更新两个数据库(彼此不同)，并且已经使用了10到15年.因此，我想开始清除它们中的垃圾.

I have an application that queries and updates two databases at the same time (different from each other) and it has already around 10 to 15 years of usage. So I would like to start clearing up the garbage from them.

我要做的一件事是删除数据库B中错误创建的所有存储过程(因为它们必须进入数据库A).

One of the things that I want to do is to remove all the stored procedures that are in database B that by mistake were created there (because they had to go in database A).

如果我一个接一个地做，很容易，因为我可以判断sp是否使用了不在该数据库中的表.但是我敢打赌，其中有很多，所以我想得到一个查询来验证这一点.

If I do one by one, is easy because I can tell if the sp uses a table that is not in that database. But I bet there are many of them, so I want to get a query that validates this.

这是来自Microsoft的链接，它对此进行了说明: http://msdn. microsoft.com/en-us/library/ms190686.aspx .但这并没有给我任何解决方案！ :S

This is the link from microsoft that explains this: http://msdn.microsoft.com/en-us/library/ms190686.aspx. But it doesn't give me any solution! :S

我尝试了以下方法:

I tried the below method: http://beyondrelational.com/blogs/jacob/archive/2009/01/28/tsql-how-to-revalidate-refresh-recompile-all-stored-procedures-in-a-database.aspx

但它对某些人有效，但对所有人而言并非如此.

But it worked for some, but not for all.

那么你们知道有什么有效的方法可以100％地起作用吗? 试用:

So do you guys know any effective method that will work 100% of the time? Tryouts:

• 重新编译>不
• sp_refreshsqlmodule>适用于大多数(但不是全部)
• sp_depends>可能起作用，因为所有SP都需要依赖表(至少大多数依赖于表).更新:这也不起作用.如果我有一个sp在两个数据库中都查找表，它将解析那些可以但不是全部的表.因此，我不能依靠如果没有依赖项，则可以删除".

预先感谢

推荐答案

没有保证的方法.

延迟名称解析(例如，如果表不存在，则给出错误)在编译时而不是在创建时适用.尽管有一些证据表明代码可能在缺少表的情况下运行

Deferred name resolution (as in if a table isn't there, give an error) applies at compile time, not create time. Although there is some evidence to show that code may run with missing tables

尽管已提出要求:请参阅建议的 SET OPTION STRICT ON 来自 Erland Sommarskog

It has been requested though: see the proposed SET OPTION STRICT ON MS Connect request which comes from Erland Sommarskog

这篇关于SQL Server-避免延迟编译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！