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

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

本文介绍了如何在 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 存储过程中搜索字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:如何在 Sql Server 2008 R2 存储过程中搜索字符串?

基础教程推荐