CODEWITHSUNDEEP
sqlstored-proceduressql-server-2000
Kings
Kings

Reputation: 1591

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

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.

Upvotes: 13

Views: 50955

Answers (5)

Mario Raúl Pérez
Mario Raúl Pérez

Reputation: 41

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

Upvotes: 2

Rakesh
Rakesh

Reputation: 1

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

Upvotes: 0

Pandian
Pandian

Reputation: 9126

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

Upvotes: 0

SWeko
SWeko

Reputation: 30902

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.

Upvotes: 6

Melanie
Melanie

Reputation: 3111

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

Upvotes: 26

Related Questions