Need help to find hard-coded IP in Stored Procedures

  • Hello all,

    I am working with many servers and diff IP... I need to know if there is any way of finding a way to get hard-coded IP in Stored Procedures <text>

    I am a novice SQL user....

  • I am making the assumption that you wish to perform tasks such as selects, updates, inserts, deletes on DBs located on a linked server.

    If that is the situation you should start by reading:

    SQL Server 2008 Books Online

    Linking Servers

    Accessing External Data

    If you are examing existing stored prodecures. For example, the following query references the Production.Product and Sales.SalesOrderDetails tables in the AdventureWorks database on the linked server SEATTLESales:

    SELECT p.Name, sod.SalesOrderID

    FROM SEATTLESales.AdventureWorks.Production.Product p

    INNER JOIN SEATTLESales.AdventureWorks.Sales.SalesOrderDetail sod

    ON p.ProductID = sod.ProductID

    ORDER BY p.Name ;

    again this is detailed in Books On Line

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Hi,

    I might not be clear with what i am looking in the first request.

    Actually i need a script to loop through all store procedures in a database and return me the name of the store procedure that contains(if any) ip address in its text.

    What i mean is if any developer has used hard coded ip address instead of servername or for any other purpose

    for example 172.10.11.12 in the text of store procedure.

    I know all the store procedures text resides in text column of syscomments table.But i dont know how to find the store procedures which have hard coded IP Address in their text.

  • SELECT definition

    FROM sys.sql_modules

    WHERE definition LIKE '%[0-9].[0-9].[0-9].[0-9]%'

    OR definition LIKE '%[0-9].[0-9].[0-9].[0-9][0-9]%'

    OR definition LIKE '%[0-9].[0-9].[0-9].[0-9][0-9][0-9]%'

    OR definition LIKE '%[0-9].[0-9].[0-9][0-9].[0-9]%'

    OR definition LIKE '%[0-9].[0-9].[0-9][0-9].[0-9][0-9]%'

    OR definition LIKE '%[0-9].[0-9].[0-9][0-9].[0-9][0-9][0-9]%'

    OR definition LIKE '%[0-9].[0-9].[0-9][0-9][0-9].[0-9]%'

    OR definition LIKE '%[0-9].[0-9].[0-9][0-9][0-9].[0-9][0-9]%'

    OR definition LIKE '%[0-9].[0-9].[0-9][0-9][0-9].[0-9][0-9][0-9]%'

    --...and so on

  • Thanks Paul for the reply.

    As per your solution i may require to right around 16 or statements for each possible combinations of IP Address.

    Is there any generic pattern for all IP Address cases to pass as a string for searching it?

    But thanks once again for the response.

    Aady82

  • Not that I can think of. Sixteen statements? Isn't it twelve? You could do it with RegEx in a CLR routine, but that seems a bit overkill.

    Actually, while I think of it, seeing as you are just comparing digits and the '.' character, adding a binary collate clause will make it 2-3 times faster:

    SELECT definition

    FROM sys.sql_modules

    WHERE definition LIKE '%[0-9].[0-9].[0-9].[0-9]%' COLLATE LATIN1_GENERAL_BIN

    OR definition LIKE '%[0-9].[0-9].[0-9].[0-9][0-9]%' COLLATE LATIN1_GENERAL_BIN

    ...

  • I suppose you can trim down the WHERE clause in Paul's good code with the minor risk of finding things you didn't intend to...

    WHERE

    definition LIKE '%[0-9].[0-9].[0-9]%'

    OR definition LIKE '%[0-9].[0-9][0-9].[0-9]%'

    OR definition LIKE '%[0-9].[0-9][0-9][0-9].[0-9]%'

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (12/14/2009)


    I suppose you can trim down the WHERE clause in Paul's good code with the minor risk of finding things you didn't intend to...

    WHERE

    definition LIKE '%[0-9].[0-9].[0-9]%'

    OR definition LIKE '%[0-9].[0-9][0-9].[0-9]%'

    OR definition LIKE '%[0-9].[0-9][0-9][0-9].[0-9]%'

    Nice optimization, I like it. (Though I think IPv4 address have four parts currently :-))

    Thanks Jeff

  • Thank you everyone for the reply.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply