Find All Items That Call a Stored Procedure

Question

Post reply

Find All Items That Call a Stored Procedure

kocheese

SSCommitted

Points: 1517
More actions
November 2, 2015 at 2:17 pm

#322227

All:
We have a process that uses a stored procedure: sp_MysteryProcedure....
The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.
One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).
So my question is multi-part.....
1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?
Thanks....
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

ScottPletcher SSC Guru Points: 101192 More actions · Answer 1

For SQL jobs, you could do something like this:

SELECT j.name, js.*

FROM msdb.dbo.sysjobsteps js

LEFT OUTER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id

WHERE js.command LIKE '%sp[_]MysteryProcedure%'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

Have you considered logging the caller from the procedure itself?

Something like this.

use tempdb;

go

/* Create a table to log calls to stored procedures */

create table LogSP (

id int identity(1,1) not null

, executed_on datetime2 not null constraint df_executed_on default(getdate())

, executed_by sysname not null constraint df_executed_by default(original_login())

, executed_app sysname not null constraint df_executed_from default(app_name())

, executed_ip sql_variant not null constraint df_executed_ip default(connectionproperty('client_net_address'))

, executed_proc sysname not null constraint df_executed_proc default(OBJECT_NAME(@@PROCID))

, constraint pk_logSP primary key (id asc) with(fillfactor=100)

) ;

go

/* create a sample stored procedure to demonstrate the logging */

create proc testProc

as

-- log access to the SP

[highlight="#ffff11"]insert logSP default values;[/highlight]

-- Do some work

go

/* execute the test */

exec testProc;

go

/* check the results */

select *

from logSP;

go

/* Clean up */

drop proc testProc;

drop table logSP;

go

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004596 More actions Follow · Answer 3

I'd do it the same way as Magoo did it above. Instead of sifting through a pile of manure to try to figure out what the horse was thinking, it's much better to just ask the horse.

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

kocheese SSCommitted Points: 1517 More actions · Answer 4

As much as I appreciate the suggestion and code, I should have stated I do not have the permission required to create a new table, nor make changes to an existing stored procedure, which is why I am stuck doing it manually

Jeff Moden SSC Guru Points: 1004596 More actions Follow · Answer 5

andrew.mills 44498 (11/3/2015)
As much as I appreciate the suggestion and code, I should have stated I do not have the permission required to create a new table, nor make changes to an existing stored procedure, which is why I am stuck doing it manually

I feel for you. I just love it when management gives folks a task and then forbids them the tools they need to get it done. If things are so locked down, they must have a DBA or two. They should do this because they're not allowing you to do it.

You could write a query to interrogate cache but it would have to run often and you would need a place to store the results. Just in case someone brings it up, you could cheat like hell and put a semi-permanent real table in Temp DB but don't do that. It will take up some memory (like any table) and if something goes awry, it'll be your butt for sidestepping "the rules".

What you could do it write it all up with the change to the proc and the creation of the table and submit it to the DBAs for review and promotion. That way, you'll be perceived as having done your best not only to solve the problem but to stay within the rules, as well.

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

kocheese SSCommitted Points: 1517 More actions · Answer 6

You are correct. There are a "bunch" of DBAs who seem to rule the land. Everything we try to do, we do not have permission but then are told when we ask them "it isn't our job. App teams own the data, we own the structure." They are not part of any development, but only do what we tell them to do because we do not have any permissions to do anything. Makes no sense to me that we give them all the scripts to run and all they do is run it. Why not eliminate the waste and just let the app teams do it. But I digress.

I do like your suggestions about giving them the 'stuff' so our part is done. Thank you for the rational approach.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

When I had a similar problem trying to find out which packages used certain tables, I used "Find in Files" option (Ctrl+Shift+F) in Notepad++.

Of course, this only works if the packages are in the file system and have a common root folder.

Scott already posted code to search in Jobs, you would be missing procedures that might call that procedure and everything that calls those procedures.

You could also use SQL Search for SQL Server objects.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kocheese SSCommitted Points: 1517 More actions · Answer 8

Luis: thank you for the help, but unfortunately this didn't help. I searched the folder in question for the stored procedure, and Notepad++ wasn't able to find any SSIS / *.dtsx file that uses this.

However: after looking at every package based on name, I was able to locate the package that calls this stored procedure. It was in the same folder I used to search automatically using Notepad++

Are you saying Notepad++ is able to search the package details (*.dtsx) file for a stored procedure call?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

andrew.mills 44498 (11/5/2015)
Luis: thank you for the help, but unfortunately this didn't help. I searched the folder in question for the stored procedure, and Notepad++ wasn't able to find any SSIS / *.dtsx file that uses this.
However: after looking at every package based on name, I was able to locate the package that calls this stored procedure. It was in the same folder I used to search automatically using Notepad++
Are you saying Notepad++ is able to search the package details (*.dtsx) file for a stored procedure call?

SSIS packages (or dtsx files) are nothing more than xml files interpreted to perform actions. Notepad++ is able to read them as plain text files (which they are even if they're "human readable"). After dealing with packages in text only mode, you'll get an idea of what each thing is referring to. It's easier if you have experience with biml.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

j-1064772 SSCertifiable Points: 5316 More actions · Answer 10

mister.magoo (11/2/2015)
...
/* create a sample stored procedure to demonstrate the logging */
create proc testProc
as
-- log access to the SP
[highlight="#ffff11"]insert logSP default values;[/highlight]
-- Do some work
go
/* execute the test */
exec testProc;
[/code]

Would you please elaborate on how the [font="Courier New"]insert logSP default values [/font]statement works - how does it manage to retrieve the required data and first of all what is the syntax that this statement obeys ?

Thanks in advance.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

j-1064772 (11/6/2015)
mister.magoo (11/2/2015)
...
/* create a sample stored procedure to demonstrate the logging */
create proc testProc
as
-- log access to the SP
[highlight="#ffff11"]insert logSP default values;[/highlight]
-- Do some work
go
/* execute the test */
exec testProc;
[/code]
Would you please elaborate on how the [font="Courier New"]insert logSP default values [/font]statement works - how does it manage to retrieve the required data and first of all what is the syntax that this statement obeys ?
Thanks in advance.

If you check the table definition, all of the columns have default constraints assigned (except for the identity column). Using DEFAULT VALUES in an INSERT INTO clause will insert a row with all the default values.

Reference: https://msdn.microsoft.com/en-us/library/ms174335.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

j-1064772 SSCertifiable Points: 5316 More actions · Answer 12

Yes, I should have known to look at books on line for the syntax, I just assumed I knew all there was to know about the INSERT statement I have been using for years.

I just got baffled on how it managed to retrieve system info. One more thing I have to do now is to look into the [font="Courier New"]constraint df_executed_from default(app_name())[/font] business.

Learn something new everyday on this forum.

Thanks

j-1064772 SSCertifiable Points: 5316 More actions · Answer 13

j-1064772

SSCertifiable

Points: 5316

November 6, 2015 at 2:06 pm

#1838347

.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

j-1064772 (11/6/2015)
Yes, I should have known to look at books on line for the syntax, I just assumed I knew all there was to know about the INSERT statement I have been using for years.
I just got baffled on how it managed to retrieve system info. One more thing I have to do now is to look into the [font="Courier New"]constraint df_executed_from default(app_name())[/font] business.
Learn something new everyday on this forum.
Thanks

The constraints are just default values for the columns, I explicitly named them like that rather than just saying default(xyz) because it's a good habit to get into or you will have lots of issues with code comparison tools showing up changes just because of an automatically named constraint.

The reason I used default values rather than passing them explicitly in the insert statement is simply because it is easier to remember "default values" than a list of function names that I don't use very often.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Find All Items That Call a Stored Procedure

Cookies on SQLServerCentral