您也可以尝试ApexSQL Search -试用版SSMS插件从ApexSQL。

您也可以尝试ApexSQL Search -试用版SSMS插件从ApexSQL。

如果你也需要schema:

SELECT   DISTINCT SCHEMA_NAME(o.schema_id),o.name,[text]
FROM     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    text LIKE '%foo%'
ORDER BY  SCHEMA_NAME(o.schema_id),o.name 

对于任何更新于SQL server 2000的SQL server:

SELECT object_name = OBJECT_NAME(sm.object_id), o.type_desc, sm.definition  
FROM sys.sql_modules AS sm  
JOIN sys.objects AS o ON sm.object_id = o.object_id  
WHERE sm.definition like '%searchString%'  
ORDER BY  o.type, o.name, o.object_id

如果有人被SQL server 2000卡住，表sql_modules不存在，所以你会使用syscomments，你会得到多个存储过程的记录大于4000个字符，但它们会有相同的c.number字段，所以你可以把这些部分组合在一起，以获得完整的存储过程文本:

    Select o.id, c.number, o.name, c.text  
    from syscomments c 
    inner join sysobjects o on o.id = c.id 
    where c.encrypted = 0 and o.type = 'P'  
      and c.id in  
     (Select id from syscomments where text like '%searchtext%')
    order by objecttype, o.name, o.id, c.number, c.colid        

首先确保在用户凭证下运行查询，并且在正确的数据库上下文中运行查询。

USE YOUR_DATABASE_NAME;

否则,系统。过程不会返回任何东西。现在运行查询如下所示:

select * from sys.procedures p 
join sys.syscomments s on p.object_id = s.id 
where text like '%YOUR_TEXT%';

另一种选择是使用INFORMATION_SCHEMA.ROUTINES。ROUTINE_DEFINITION，但是请注意它只保存例程的有限数量的字符(即，前4000个字符)。

select * from YOUR_DATABASE_NAME.INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%YOUR_TEXT%';

我在Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)上进行测试

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

试试这个.....