如何在Sql Server 2008 R2存储过程中搜索字符串? [英] how to search Sql Server 2008 R2 stored procedures for a string?

问题描述

我正在将旧版SQLS2k迁移到2008R2，看来所有数据访问都是通过存储的proc完成的，所有自定义查询都使用旧版*= =*外部联接语法.有超过一百个proc，所以我不想单独打开每个进程以查看它是否使用该语法(大多数不会)，有没有一种方法可以查询元数据以获取proc/功能/视图的列表/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存储过程中搜索字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！