检查 SQL Server 存储过程、视图和函数的有效性

• 下载源文件 - 171.11 KB

引言

前几天我整理我们的 SQL 数据库。这涉及到删除一些过时的列。通常当我这样做时，我会使用一个 SQL 脚本来搜索我正在删除的列的所有出现，然后逐一检查并修复每个存储过程。我记得在我有限的 Oracle 经验中，在 Oracle 中可以判断一个存储过程是否有效。我认为这对 SQL Server 来说是一个非常有用的功能。于是 Db Validator 就诞生了。

快速入门

如果您对代码或项目构建不感兴趣，那么输出已包含在分发版中。

• 下载源文件。
• 修改 bin/debug 文件夹中的 dataconfiguration.config 文件，指向您的服务器和数据库。
• 从命令行运行 DbValidator.exe。例如：dbvalidator > c:\output.txt
• 检查输出。任何编译错误都会与错误详情一起列出。

就是这样。我发现这节省了很多时间，并且也有助于让我确信我没有在不知情的情况下破坏数据库的任何部分。

工作原理

当我着手做这件事时，有人提醒我 SQL Enterprise Manager 中的“检查语法”功能。如果我能复制这个功能，那就理想了，因为我不想实际编译数据库对象——只想模拟编译。所以我打开 SQL Profiler 并记录了按下“检查语法”按钮时执行的语句。有趣的是，按下此按钮时会发生以下情况：

SET NOEXEC ON
GO
CREATE PROC....etc
GO
SET NOEXEC OFF
GO
SET PARSEONLY OFF
GO

我以前从未遇到过这些 SET 命令。在 BOL 中查找它们，它们告诉我您已经猜到的内容：

• SET NOEXEC “编译 Transact-SQL 语句的每个批处理，但不执行它们”
• SET PARSEONLY “检查每个 Transact-SQL 语句的语法，并返回任何错误消息，而不编译或执行该语句”

好的，很明显 SET NOEXEC 是我想要的。那么为什么 Enterprise Manager 在完成编译后还会关闭 SET PARSEONLY 呢？我不明白这一点，但我决定无论如何都要精确复制 Enterprise Manager 所做的。

那么，我们的程序需要做什么？

1. 获取数据库中所有是存储过程、函数或视图的对象列表
2. 获取每个对象的定义。即 Create Proc / view / function 命令
3. 执行创建命令，但将命令包装在 Enterprise Manager 所做的 SET NOEXEC / PARSEONLY 位中
4. 检测引发的任何错误并将其记录到控制台

实现细节

在首次运行此代码时，我总是会遇到某些存储过程总是出错。我发现这是因为它们依赖于启用了引号标识符（即，允许如下语句：if isnull(@ErrMsg, '') <> ""）。显然这是糟糕的代码，应该通过将 "" 替换为 '' 来修复，但这种情况向我强调了这样一个事实：所有存储过程等都与它们的 ANSI NULL 和 QUOTED INDENTIFIER 设置一起存储在数据库中。为了让我的程序正常工作，我需要弄清楚这些设置应该是什么。以下 SQL 语句正在执行此操作：

select name, 
     OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') as quoted_ident_on, 
     OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') as ansi_nulls_on, 
     user_name(o.uid) owner 

from sysobjects o
where type in ('P', 'V', 'FN') and 

category = 0

除了获取对象名称之外，我还获取了对象的 QUOTED INDENTIFIER 和 ANSI NULL 设置。

您还会从代码中注意到我使用了 Enterprise Library 数据访问块。这是来自 Microsoft Patterns and Practices 团队的一个非常有用的块。如果您没有安装 enterprise library，它应该仍然可以工作，但您可能会发现，如果您遇到任何数据访问错误，这些错误可能会被一个抱怨缺乏性能计数器的错误所掩盖。另请注意，我使用的是该库的 2005 年 6 月版本。

结论

正如您所见，这是一个相当简单的应用程序，但我希望您会发现它和我一样有用。我想未来的增强功能可能是一个 GUI，为每个对象显示勾号和叉号，但目前它很好地满足了我的需求。

历史

• 2006 年 2 月 13 日：初始发布