在 SQL Server 2000 中查找存储过程名称及其内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14260914/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find a stored procedure name with its content in SQL Server 2000
提问by Kings
For a stored procedure, I have its full source code. But the nameof that stored procedure has been lost. In this database, there are hundreds of stored procedures.
对于存储过程，我有它的完整源代码。但是那个存储过程的名字已经丢失了。在这个数据库中，有数百个存储过程。
So is there a way by which I can find out the nameof the stored procedure by using its contents or by using any of the variables in the contents?
那么有没有办法通过使用存储过程的内容或使用内容中的任何变量来找出存储过程的名称？
This is puzzling me a lot. A help would be sincerley appreciated.
这让我很困惑。将真诚地感谢帮助。
回答by Melanie
Try this:
尝试这个：
select * from sysobjects where id in
(select id from syscomments where text like '%exec%')
order by [name]
where 'exec' is the text you're searching for. This query will search views also, just fyi
其中 'exec' 是您要搜索的文本。此查询也将搜索视图，仅供参考
回答by SWeko
If the texts of the stored procedures are not encrypted, Sql Server keeps the full text of the procedure in the syscommentstable with an idfield that is referencing the sysobjectstable, where the actual name is stored.
如果存储过程的文本未加密，则 Sql Server 会在syscomments表中保留过程的完整文本，其中包含一个id引用该sysobjects表的字段，其中存储了实际名称。
So, find some representative line from the stored procedure, that is unlikely to be in another place, and do:
因此，从存储过程中找到一些不太可能出现在其他地方的代表性行，然后执行以下操作：
select o.name, c.text
from syscomments c
inner join sysobjects o on o.id = c.id
where c.text like '%<representative_line>%'
and o.type='P' -- this means filter procedures only
This should hopefully return just a few procedures that you can check by hand.
这应该会返回一些您可以手动检查的程序。
回答by Mario Raúl Pérez
There is another approach, more readable and memorable:
还有另一种方法，更具可读性和记忆性：
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%AlgunaPalabraQueTeAcuerdes%'
AND ROUTINE_TYPE='PROCEDURE'
This works at least in sql 2008 and newer versions. Fuente
这至少适用于 sql 2008 和更新版本。丰特
回答by Rakesh
select object_name(id) from syscomments where text like '%exec%'
回答by Pandian
Try this Query it will get the Procedure Name that contains the Given word or Field
试试这个查询，它将获得包含给定单词或字段的过程名称
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%Any-Field (OR) WORD%'
