Need help to find hard-coded IP in Stored Procedures

Question

Need help to find hard-coded IP in Stored Procedures

Aady82

SSC Veteran

Points: 212
More actions
December 13, 2009 at 12:45 pm

#218726

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....

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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]

Aady82 SSC Veteran Points: 212 More actions · Answer 2

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.

Paul White SSC Guru Points: 150468 More actions · Answer 3

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

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Aady82 SSC Veteran Points: 212 More actions · Answer 4

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

Paul White SSC Guru Points: 150468 More actions · Answer 5

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

...

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004600 More actions · Answer 6

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)

Paul White SSC Guru Points: 150468 More actions · Answer 7

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

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Aady82 SSC Veteran Points: 212 More actions · Answer 8

Aady82

SSC Veteran

Points: 212

December 16, 2009 at 5:40 pm

#1093361

Thank you everyone for the reply.

Need help to find hard-coded IP in Stored Procedures

Cookies on SQLServerCentral