我如何找到一个存储过程包含<文本>?

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

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

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

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%Foo%'

抓取自己的免费的 红门SQL搜索工具的副本，并开始享受在SQL Server搜索!: -)

它是一个伟大的和非常有用的工具，是的!它是完全，绝对免费的任何类型的使用。

如果你也需要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

你也可以尝试ApexSQL中的ApexSQL搜索 -试用版SSMS插件。

SELECT s.name + '.' + o.name ProcedureName
, c.text ProcedureSteps
FROM   sys.syscomments c
INNER JOIN
sys.objects o
ON
c.id = o.object_id
INNER JOIN
sys.schemas s
ON
o.schema_id = s.schema_id
WHERE  o.type = 'P'
AND c.text LIKE N'%XXXX%'
ORDER BY s.name + '.' + o.name
, c.colid

该查询返回在存储过程中引用“XXXX”的任何存储过程的名称和内容。

这在查找引用特定表/视图/过程的过程时非常有用

select * from sys.system_objects
where name like '%cdc%'

SELECT * FROM sys.procedures WHERE Name LIKE '%CompanySpecialisation%'

这是我在SQL 2012中编写的，以选择所有名称为“CompanySpecialisation”的存储过程。

我接受了卡西夫的回答，把所有的答案结合在一起。奇怪的是，有时我在其中一个选择中找到了结果，而在另一个选择中却没有。为了安全起见，当我在寻找某个东西时，我会运行这3个。希望这能有所帮助:

DECLARE @SearchText varchar(1000) = 'mytext';


SELECT DISTINCT SPName
FROM (
(SELECT ROUTINE_NAME SPName
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%'
AND ROUTINE_TYPE='PROCEDURE')
UNION ALL
(SELECT OBJECT_NAME(id) SPName
FROM SYSCOMMENTS
WHERE [text] LIKE '%' + @SearchText + '%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id))
UNION ALL
(SELECT OBJECT_NAME(object_id) SPName
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%' + @SearchText + '%')
) AS T
ORDER BY T.SPName

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

我尝试了上面的例子，但它没有显示超过4000个字符，然后我对它进行了一点点修改，并能够得到整个存储过程定义。请参阅下面更新的脚本-

SELECT SCHEMA_NAME(O.SCHEMA_ID) [SCHEMA_NAME], O.NAME, OBJECT_DEFINITION(OBJECT_ID) 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  OBJECT_DEFINITION(OBJECT_ID) LIKE '%FOO%'
ORDER BY  SCHEMA_NAME(O.SCHEMA_ID), O.NAME

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'`enter code here`


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

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

试试这个.....

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

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)上进行测试

sp_msforeachdb 'use ?;select name,''?'' from sys.procedures where object_definition(object_id) like ''%text%'''

这将在所有数据库的所有存储过程中进行搜索。这也适用于较长的程序。

我使用这个脚本。如果您将XML Comments更改为显示为黄色背景上的黑色文本，则可以在结果的XML列中突出显示您正在寻找的文本。(工具->选项->环境->字体和颜色[显示项:XML注释]

    ---------------------------------------------
--------------   Start  FINDTEXT   ----------
---------------------------------------------


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
GO
DECLARE @SearchString VARCHAR(MAX)
SET @SearchString = 'the text you''re looking for'
DECLARE @OverrideSearchStringWith VARCHAR(MAX)
--#############################################################################
-- Use Escape chars in Brackets []  like [%] to find percent char.
--#############################################################################


DECLARE @ReturnLen INT
SET @ReturnLen = 50;
with    lastrun
as (select    DEPS.OBJECT_ID
,MAX(last_execution_time) as LastRun
from      sys.dm_exec_procedure_stats DEPS
group by  deps.object_id
)
SELECT  OL.Type
,OBJECT_NAME(OL.Obj_ID) AS 'Name'
,LTRIM(RTRIM(REPLACE(SUBSTRING(REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), ''), CHARINDEX(@SearchString, OBJECT_DEFINITION(OL.Obj_ID)) - @ReturnLen, @ReturnLen * 2), @SearchString, '   ***-->>' + @SearchString + '<<--***  '))) AS SourceLine
,CAST(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(MAX), REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), '')), '&', '(A M P)'), '<', '(L T)'), '>', '(G T)'), @SearchString, '<!-->' + @SearchString + '<-->') AS XML) AS 'Hilight Search'
,(SELECT [processing-instruction(A)] = REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), '')
FOR
XML PATH('')
,TYPE
) AS 'code'
,Modded AS Modified
,LastRun as LastRun
FROM    (SELECT CASE P.type
WHEN 'P' THEN 'Proc'
WHEN 'V' THEN 'View'
WHEN 'TR' THEN 'Trig'
ELSE 'Func'
END AS 'Type'
,P.OBJECT_ID AS OBJ_id
,P.modify_Date AS modded
,LastRun.LastRun
FROM   sys.Objects P WITH (NOLOCK)
LEFT join lastrun on P.object_id = lastrun.object_id
WHERE  OBJECT_DEFINITION(p.OBJECT_ID) LIKE '%' + @SearchString + '%'
AND type IN ('P', 'V', 'TR', 'FN', 'IF', 'TF')
--   AND lastrun.LastRun  IS NOT null
) OL
OPTION  (FAST 10)


---------------------------------------------
----------------    END     -----------------
---------------------------------------------
---------------------------------------------

下面是使用Losbear制作的联合版本，但修改后它也输出模式名:

DECLARE @SearchText varchar(1000) = 'searchtext';


SELECT DISTINCT sc.SPECIFIC_SCHEMA as 'Schema', sp.SPName as 'Name'
FROM (
(SELECT ROUTINE_NAME SPName
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%'
AND ROUTINE_TYPE='PROCEDURE')
UNION ALL
(SELECT OBJECT_NAME(id) SPName
FROM SYSCOMMENTS
WHERE [text] LIKE '%' + @SearchText + '%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id))
UNION ALL
(SELECT OBJECT_NAME(object_id) SPName
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%' + @SearchText + '%')
) AS sp
CROSS APPLY (select SPECIFIC_SCHEMA from INFORMATION_SCHEMA.ROUTINES r where r.ROUTINE_NAME = sp.SPName) sc
ORDER BY 1

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

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

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

对于任何更新于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

试试下面的代码:

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]