SQL 2005 - 搜索文本的存储过程(并非所有文本都被搜索) [英] SQL 2005 - Search stored procedures for text (Not all text is being searched)

问题描述

以下代码似乎没有搜索整个例程定义.

The following bits of code do not seem to be searching the entire routine definition.

代码块 1:

select top 50 * from information_schema.routines
where routine_definition like '%09/01/2008%' and specific_Name like '%NET'

代码块 2:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%EffectiveDate%' AND ROUTINE_TYPE='PROCEDURE' and ROUTINE_NAME like '%NET'

我知道这些 SQL 代码在大多数情况下都有效.问题是这样的:当我为EffectiveDate"运行这个时，它被埋在一些存储过程中的 ~800 行，这些存储过程永远不会出现在结果中.就好像喜欢"只能搜索这么深.

I know for a fact that these bits of SQL work under most circumstances. The problem is this: When I run this for "EffectiveDate" which is buried at line ~800 in a few stored procedures, these stored procedures never show up in the results. It's as if "like" only searches so deep.

关于解决这个问题的任何提示?

Any tips on fixing this?

我想在整个存储过程中搜索指定的文本.

I want to search the ENTIRE stored procedure for the specified text.

谢谢！

推荐答案

select * 
FROM   INFORMATION_SCHEMA.ROUTINES 
WHERE  OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0 
       and OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)) like '%EffectiveDate%' 
       AND ROUTINE_TYPE='PROCEDURE'
       AND ROUTINE_NAME like '%NET'

改用对象定义.

这篇关于SQL 2005 - 搜索文本的存储过程(并非所有文本都被搜索)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！