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.

Beautify

Answer 1

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

Statistical

Answer 2

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.

Rorke

this is also good. But can't select more than one 'Answer'. Thnaks : ) – Beautify

Answer 3

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

Soniasonic

Answer 4

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%'

Christcrossrow

sys.procedures doesn't exist on SQL 2000 – Scuba

True. Mentioned Prob is for SQL 2000 !! – Beautify

Answer 5

select object_name(id) from syscomments where text like '%exec%'

Muncey