用ChatGPT解决这个技术问题 Extra ChatGPT

如何找到包含 <text> 的存储过程？

我需要在 SQL Server 2008 中搜索包含数据库字段名称或变量名称的存储过程。

C
Community 
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%'
请注意，routine_definition 被裁剪为 4000 个字符，以防万一您的程序很长。请改用 OBJECTPROPERTY 方法。
@PeteT 所说的一百万倍。几年前，这个 4000 个字符的限制真的让我很痛苦。
包括架构名称：OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema]
您能否对这 3 个查询中的每一个的作用以及它们之间的区别进行一些解释？
如果能详细了解每个查询的作用以及它们的不同之处或为什么一个比另一个更好，那就太好了。这类答案鼓励经验不足的工程师盲目/复制粘贴，并让更多高级工程师使用新的 RDBMS（如 SQL Server）感到沮丧。
m
marc_s

获取一份免费 Red-Gate SQL Search 工具的副本，开始享受在 SQL Server 中的搜索吧！ :-)

https://i.stack.imgur.com/qCjKb.png

这是一个很棒且非常有用的工具，是的！它完全、完全免费，可用于任何用途。

这是一个很好的工具，在我遇到这个问题之前我已经在使用它了。值得注意的是，它默认将搜索结果限制为 150 个。查看此forum post，了解有关如何提高该限制的信息。
L
Losbear

我接受了 Kashif 的回答，并将他们全部联合起来。奇怪的是，有时，我在其中一个选择中找到了结果，但在另一个选择中却没有。所以为了安全起见，当我在找东西时，我会运行所有 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
J
Jeferson Tenorio

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

https://i.stack.imgur.com/4mDyt.jpg

T
Tom

如果您还需要架构： 

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
Y
Yuci

首先确保您在您的用户凭据下以及在正确的数据库上下文中运行查询。 

USE YOUR_DATABASE_NAME;

否则， sys.procedures 不会返回任何东西。现在运行如下查询： 

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

G
Geoff Appleford 
SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%FieldName%' 
AND ROUTINE_TYPE='PROCEDURE'
不过要小心这一点，因为它 only 是一个子字符串匹配。 “FieldName”可能出现在注释、字符串常量或类似内容中。另外，我怀疑寻找 [whatever] 会捕获 whatever （当您寻找模式限定的标识符时变得更加重要）。
请参阅上面已接受答案中@PeteT 的评论。 INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION 被裁剪为 4000 个字符。因此，如果您的存储过程超过 4000 个字符，则此方法将不起作用。
S
Sandy

我尝试了上面的示例，但它没有显示超过 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
M
Mourad

对于任何比 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
Y
Yu Hao 
select * from sys.system_objects
where name like '%cdc%'
A
Alexander

这是使用 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
K
Kamal Pratap

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

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

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

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

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

J
Jitendra Sawant 
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%FOO%'
它是否还将存储过程裁剪为 4000 个字符？
M
Muhammad Usman 
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
A
Akshey Bhat 
sp_msforeachdb 'use ?;select name,''?'' from sys.procedures where object_definition(object_id) like ''%text%'''

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

u
user2132692 
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”的任何存储过程的名称和内容。

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

g
gotqn

在 SP.. {Dinesh Baskaran} 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
E
Er Ketan Vavadiya 
SELECT OBJECT_NAME(id)     
FROM syscomments 
WHERE [text] LIKE '%Name%' 
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
GROUP BY OBJECT_NAME(id)

尝试这个 .....

J
Jay Wheeler

我使用这个脚本。如果您将 XML 注释更改为在黄色背景上显示为黑色文本，您将获得在结果的 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     -----------------
    ---------------------------------------------
    ---------------------------------------------
A
AliNajafZadeh

尝试以下代码： 

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]
这将返回所有存储过程，忽略搜索文本。

关注公众号,不定期副业成功案例分享
关注公众号

不定期副业成功案例分享

想领先一步获取最新的外包任务吗？

立即订阅

HuntsBot(狩猎机器人)，一站式外包任务、远程工作、产品创意分享与订阅平台，支持钉钉、飞书、企业微信、邮箱、Telegram机器人订阅。平台会以及时、稳定、可靠的技术把外包任务需求、远程工作机会、产品创意等推送给每一位订阅的用户。

简体中文 English

平台

支持

友情链接

联系我们

在使用过程中有任何问题或建议，可以通过以下方式联系我们：

微信公众号: 火星来客

Email: huntsbot@xinbeitime.com

Copyright© 2022-2023 www.huntsbot.com 浙ICP备2022000860号-4 All Rights Reserved.