SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%FOO%'

如何找到一个包含文本或字符串的存储过程

很多时候，我们需要在存储过程中查找文本或字符串。下面是查找包含文本的查询。

SELECT OBJECT_NAME(id) 
FROM SYSCOMMENTS 
WHERE [text] LIKE '%Text%' 
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
GROUP BY OBJECT_NAME(id)

欲了解更多信息，请查看下面给出的URL。

http://www.freshcodehub.com/Article/34/how-to-find-a-stored-procedure-containing-text-or-string

试试下面的代码:

DECLARE @SearchText NVARCHAR(2000) = N'YourText';
SELECT   DISTINCT SCHEMA_NAME(o.schema_id),o.name,[text] AS MainTextProc
FROM     sys.syscomments AS c
         INNER JOIN sys.objects AS o ON c.id = o.[object_id]
         INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE    c.[text] LIKE '%'+ @SearchText +'%'
ORDER BY  SCHEMA_NAME(o.schema_id),o.[name]

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%FieldName%' 
AND ROUTINE_TYPE='PROCEDURE'

用于在SP中查找文本的存储过程。{迪尼希·巴斯卡兰}新潮全球系统私人有限公司

  create Procedure [dbo].[TextFinder]
 (@Text varchar(500),@Type varchar(2)=NULL)
AS
BEGIN





SELECT DISTINCT o.name AS ObjectName, 
CASE o.xtype 
WHEN 'C' THEN 'CHECK constraint ' 
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure' 
WHEN 'S' THEN 'System table'  
WHEN 'TF' THEN 'Table function' 
WHEN 'TR' THEN 'Trigger'  
WHEN 'U' THEN 'User table' 
WHEN 'V' THEN 'View' 
WHEN 'X' THEN 'Extended stored procedure' 
ELSE o.xtype 
END AS ObjectType,  

ISNULL( p.Name, '[db]') AS Location

FROM syscomments c

INNER JOIN sysobjects o ON c.id=o.id

LEFT JOIN sysobjects p ON o.Parent_obj=p.id

WHERE c.text LIKE '%' + @Text + '%' and

o.xtype = case when @Type IS NULL then o.xtype  else @Type end 


ORDER BY Location, ObjectName



END

SELECT OBJECT_NAME(id)     
FROM syscomments 
WHERE [text] LIKE '%Name%' 
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
GROUP BY OBJECT_NAME(id)

试试这个.....