Find a stored procedure name with its content in SQL Server 2000

Question

For a stored procedure, I have its full source code. But the name of 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 name of 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.

Answer 1

If the texts of the stored procedures are not encrypted, Sql Server keeps the full text of the procedure in the syscomments table with an id field that is referencing the sysobjects table, where the actual name is stored.

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.

Answer 2

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