Need a script to find hard coded IP Address in Store procedures

Hi all,I am a novice to sql server. I need a script which can loop through all store procedures in a database and return me the store procedure name which contain hard coded ip address in its text.

Hi Madhivan,Thanks for the response.But my problem is i dont have the IP Address which i am searching for.I need to send a generic IP Address pattern to the above query which would return me the results.I am not able to find IP Address pattern to send to the above query.Thanks,aja4u

Problem is that the four parts of the IP address can each be shorter than 3 characters, and madhi's solution will only find addresses with all parts having 3 characters.
There are 81 possible combinations of 1, 2 and 3 character strings in four positions - hopefully this can be of help:select
x.definition
from sys.all_sql_modules x,
(select 1 as ct union all select 2 union all select 3) y1,
(select 1 as ct union all select 2 union all select 3) y2,
(select 1 as ct union all select 2 union all select 3) y3,
(select 1 as ct union all select 2 union all select 3) y4
where x.definition like
'%' + REPLICATE('[0-9]', y1.ct) + '.' + REPLICATE('[0-9]', y2.ct) + '.' + REPLICATE('[0-9]', y3.ct) + '.' + REPLICATE('[0-9]', y4.ct) + '%'
*******
It may take some time to finish!

Come to think of it, you only need to check for two dots with 1, 2 or 3 numerals between them:select
x.* from sys.all_sql_modules x
where (x.Definition LIKE '%.[0-9].%' or x.Definition LIKE '%.[0-9][0-9].%' or x.Definition LIKE '%.[0-9][0-9][0-9].%')
and OBJECTPROPERTY(x.[object_id], 'IsMSShipped') = 0
That should reduce processing time.
I added the check for IsMSShipped going by the results I was getting in my installation.

I have tried the same logic in the information_schema.routines view.
select 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].%')
Hope, this will be bit faster.
Thanks and Regards,
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/

Note that information_schema.routines only shows stored procedures and functions that can be accessed by the current user in the current database. So for instance you won't see trigger code ...