x

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

more ▼

asked Jan 29, 2013 at 10:00 AM in Default

avatar image

askmlx121
2.5k 72 76 83

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jan 29, 2013 at 10:11 AM

avatar image

anthony.green
2.9k 1 4 6

Ya its useful.......for jobs ............. select * from msdb.dbo.sysjobsteps where command like '%192.%'

Jan 29, 2013 at 10:43 AM askmlx121
(comments are locked)
10|1200 characters needed characters left

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 on identifying long running queries shows how to set up the trace events.

more ▼

answered Jan 29, 2013 at 10:20 AM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

ok grant fritchey.............thank u

Jan 29, 2013 at 10:43 AM askmlx121
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2030
x476
x74
x39
x9

asked: Jan 29, 2013 at 10:00 AM

Seen: 1200 times

Last Updated: Jan 29, 2013 at 10:44 AM

Copyright 2017 Redgate Software. Privacy Policy