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**
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.
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/
12 People are following this question.
