question

askmlx121 avatar image
askmlx121 asked

how to get the value used in SP or Views or Functions or SQL Jobs?

Hi, Another Challenge/New things to learn i have scenario like **I want to list the stored procedures,view,Function ,SQL jobs if used the IP address inside the stored procedure/views/Function/ SQL jobs**. Is it possible to get? I have one sample below for understanding the concept sample function: CREATE FUNCTION [dbo].[GetclientIP] () RETURNS varchar(255) AS BEGIN DECLARE @IP_Address varchar(255); SELECT @IP_Address = client_ip_address FROM clientaddress---sample table WHERE Session_id = 192.168.1.78 Return @IP_Address; END The above Function used the ip address '192.168.1.78'. so I want to list out **if Any of IP Address SP/Views/function/sql jobs used means I want to list out**
sql-server-2005stored-proceduresfunctionsviewsip-address
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered
Query sys.sql_modules in each database filtering on the definition column for procs, views etc. Query msdb.dbo.sysjobsteps filtering on the command column for jobs.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ya its useful.......for jobs ............. select * from msdb.dbo.sysjobsteps where command like '%192.%'
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
If I understand the question, and I'm not entirely sure I do, you need to look at setting up a trace events job within SQL SErver. You can make it so that it captures the remote procedure call and the sql batch to see what parameters and values are passed to a query. In order to see individual statements you'd have to do a statement level trace, but those are very expensive both in terms of impact on the server and the amount of data you'll be collecting. Exercise extreme caution if you go that route. [Gail Shaw's article][1] on identifying long running queries shows how to set up the trace events. [1]: http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ok grant fritchey.............thank u
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.