December 13, 2009 at 12:45 pm
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....
December 13, 2009 at 2:05 pm
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
December 13, 2009 at 3:21 pm
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.
December 13, 2009 at 9:57 pm
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
December 14, 2009 at 5:46 pm
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
December 14, 2009 at 7:39 pm
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
...
December 14, 2009 at 7:51 pm
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
Change is inevitable... Change for the better is not.
December 14, 2009 at 9:21 pm
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
December 16, 2009 at 5:40 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy