如何在 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.
相关文章