用于在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

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

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

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 ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%FieldName%' 
AND ROUTINE_TYPE='PROCEDURE'

自己抓一个免费的红门SQL搜索工具，开始享受在SQL Server搜索!：-)

这是一个非常有用的工具，是的!它是完全，绝对免费的任何用途。

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

试试这个.....