如何在 Sql Server 2008 R2 存储过程中搜索字符串?

问题描述

我正在将旧版 SQLS2k 迁移到 2008R2，似乎所有数据访问都是通过存储过程完成的，任何自定义查询都使用旧版 *= =*外连接语法.有超过一百个过程，所以我不想单独打开每个过程以查看它是否使用该语法(大多数不会)，有没有一种方法可以查询过程/函数/视图列表的元数据/triggers，然后循环搜索 *= 或 =* 字符串，打印出违规对象的名称?

I'm migrating a legacy SQLS2k to 2008R2, and it seems all data access was done through stored procs, and any custom queries use the legacy *= =* outer join syntax. There are upwards of a hundred procs so I don't want to open each one individually to see if it uses that syntax (most wouldn't), is there a way I can query the metadata for a list of procs/functions/views/triggers, then loop through searching for the *= or =* strings, printing out the name of the offending object?

我的背景是 oracle，我知道如何在那里找到元数据视图，但我对 Sql Server 有点陌生.不能降级兼容版本.

My background is oracle, I know how to find the metadata views there, but I'm a bit new to Sql Server. Downgrading the compatibility version is not an option.

谢谢！

推荐答案

免费 Red Gate SQL 搜索?

Free Red Gate SQL Search?

或者查询sys.sql_modules

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%=*%' OR definition LIKE '%*=%'

注意:INFORMATION_SCHEMA 视图和系统注释会截断定义，因此不可靠.

Note: INFORMATION_SCHEMA views and syscomments truncate the definition so are unreliable.

这篇关于如何在 Sql Server 2008 R2 存储过程中搜索字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！