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
0
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%FieldName%'
AND ROUTINE_TYPE='PROCEDURE'
0
如果您还需要架构：
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
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
0
对于任何比SQL Server 2000更新的SQL服务器：
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
select * from sys.system_objects
where name like '%cdc%'
6
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
0
如何查找包含文本或字符串的存储过程
很多时候我们需要在存储过程中找到文本或字符串 . 这是查找包含文本的查询 .
SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text] LIKE '%Text%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
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”的任何存储过程的名称和内容 .
在查找引用特定表/视图/过程的过程时，这是有用的
1
SELECT * FROM sys.procedures WHERE Name LIKE '%CompanySpecialisation%'
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%FOO%'
0
在SP中查找文本的存储过程.. Trendy Global Systems pvt ltd
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
24
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%Name%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
试试这个 .....
10
sp_msforeachdb 'use ?;select name,''?'' from sys.procedures where object_definition(object_id) like ''%text%'''
---------------------------------------------
-------------- 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 -----------------
---------------------------------------------
---------------------------------------------
2
这是使用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
20 回答
首先确保您在用户凭据下以及正确的数据库上下文中运行查询 .
否则，sys.procedures将不返回任何内容 . 现在运行查询如下：
另一种选择是使用INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION，但要注意它只保存例程的有限数量的字符（即前4000个字符） .
我在Microsoft SQL Server 2008 R2（SP1）上测试 - 10.50.2500.0（X64）
grab 自己的 free Red-Gate SQL Search工具副本，开始享受在SQL Server中搜索！ :-)
这是一个非常有用的工具，并且 YES! 完全免费，任何用途都是免费的 .
您也可以尝试使用ApexSQL中的ApexSQL Search免费SSMS插件 .
我接受了Kashif的回答并将他们所有人联合起来 . 奇怪的是，有时候，我在其中一个选择中找到了结果而在另一个选择中找不到结果 . 所以为了安全起见，我在寻找东西时全部运行3 . 希望这可以帮助：
如果您还需要架构：
我试过上面的例子，但它没有显示超过4000个字符然后我修改了一点点，并能够获得整个存储过程定义 . 请参阅下面的更新脚本 -
对于任何比SQL Server 2000更新的SQL服务器：
如果有人坚持使用SQL Server 2000，那么表sql_modules就不存在了，所以你会使用syscomments，你会得到多条记录，用于存储大于4000个字符的过程，但它们将具有相同的 c.number 字段，因此你可以对这些部分进行分组一起获取完整的存储过程文本：
您也可以尝试this utility program我写道，使用快速工具比编写SQL查询更有效 . 它还将搜索您的源代码文件 .
如何查找包含文本或字符串的存储过程
很多时候我们需要在存储过程中找到文本或字符串 . 这是查找包含文本的查询 .
有关详细信息，请查看下面给出的URL .
http://www.freshcodehub.com/Article/34/how-to-find-a-stored-procedure-containing-text-or-string
此查询返回存储过程中引用“XXXX”的任何存储过程的名称和内容 .
在查找引用特定表/视图/过程的过程时，这是有用的
这是我在SQL 2012中编写的选择所有存储过程的地方，如“CompanySpecialisation”这样的名称
在SP中查找文本的存储过程.. Trendy Global Systems pvt ltd
试试这个 .....
这将搜索所有数据库的所有存储过程 . 这也适用于长程序 .
我用这个脚本 . 如果更改XML注释以在黄色背景上显示为黑色文本，则可以在结果的xml列中突出显示要查找的文本 . （工具 - >选项 - >环境 - >字体和颜色[显示项目：XML注释]
这是使用Losbear制作的联合版本，但经过修改以便它还输出模式名称：