SQL Server Performance

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

Discussion in 'SQL Server 2008 General Developer Questions' started by aja4u, Dec 13, 2009.

  1. aja4u New Member

    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.
  2. Madhivanan Moderator

    select routine_name from information_schema.routines
    where object_definition(object_id(routine_name)) like '%your ip address%'
  3. aja4u New Member

    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
  4. Madhivanan Moderator

    select
    routine_name from information_schema.routines
    where object_definition(object_id(routine_name)) like '%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9].[0-9][0-9][0-9]%'
  5. Adriaan New Member

    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!
  6. FrankKalis Moderator

    This is actually a case where I would rather script out all code and then use a client language that support regular expressions. That's way easier than trying this in T-SQL.
  7. Adriaan New Member

    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.
  8. venkatesanj@hcl.in New Member

    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/
  9. Adriaan New Member

    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 ...
  10. Madhivanan Moderator

    [quote user="venkatesanj@hcl.in"]
    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/
    [/quote]
    1 Information_schema.routines will have only 4000 characters and no gaurantee it would give you data
    2 How did you arrive at the conclusion that your method is bit faster? [;)]
    3 You may need to edit your blog post accordingly
    http://venkattechnicalblog.blogspot.com/2010/01/search-string-in-stored-procedure.html

Share This Page