Search string in the stored procedure

Searching a string in the stored procedures:

Am writing after a very long break :-) I got a peculiar question in one of the forum and wish to start my article from the question started.

The query is, "Am having hard coded ip address values in the stored procedure and I need to find all the stored procedures" How to find it?

Let's see, How can we address the above said issue.

Am creating the stored procedure

CREATE PROCEDURE Venkatproc
AS BEGIN
DECLARE @IP VARCHAR(100)
SET @IP ='12.34.56.78'
END

All stored procedures/function details can be fetched from a system related view information_schema.routines

Let's see the same,

select * from information_schema.routines

The above query will return all the stored procedures and functions available in the database + properties and details of the objects like owner of the object, which database, when its created, whats the definition etc..,

Let move the problem, We can use the like operator to identify any hard coded IP address and it needs to be searched in the routiine definition column.

select x.ROUTINE_DEFINITION,x.specific_name,x.* from information_schema.routines x
where (x.ROUTINE_DEFINITION LIKE '%.[0-9].%' or x.ROUTINE_DEFINITION LIKE '%.[0-9][0-9].%' or x.ROUTINE_DEFINITION LIKE '%.[0-9][0-9][0-9].%')

Happy Learning!!!

Thanks and Regards,

Venkatesan Prabu .J