[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Find a location of stored procedure

Posted on 2014-08-25
13
Medium Priority
?
165 Views
Last Modified: 2014-08-25
A SQL Server job has called a stored procedure but is there a quick way to check where does this stored procedure located ?

Tks

EXEC      @return_value = [dbo].[CleanDBLog]
0
Comment
Question by:AXISHK
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 400 total points
ID: 40282543
Hi,

Stored Procedures are stored in  sys.procedures tables of the Database.

select * from sys.procedures
0
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 1200 total points
ID: 40282582
Do you want to find in which database is the stored procedure?
Then this code can help you:
EXECUTE sp_MSForEachDB 
	'USE ?;
	SELECT DB_NAME();
    SELECT * FROM sys.procedures WHERE name = ''WriteStoredProcedureNameHere'''

Open in new window

0
 

Author Comment

by:AXISHK
ID: 40282594
One more check, how to list all the SQL  jobs ? Tks
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40282602
Try this

SELECT job_id, [name] FROM msdb.dbo.sysjobs

Open in new window

0
 

Author Comment

by:AXISHK
ID: 40282697
Run with error, any idea ? Tks
TSQL.png
0
 

Author Comment

by:AXISHK
ID: 40282700
Is It possible to list the active SQL job only ?
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40282702
Verify the quotation. Because it's inside a string you need to duplicate quotations.
Just check with my example. There's two '' before and after the stored procedure name. Then one more for close the string.
0
 

Author Comment

by:AXISHK
ID: 40282712
Still not correct,..

EXECUTE sp_MSForEachDB
      'USE ?;
      SELECT DB_NAME();
    SELECT * FROM sys.procedures WHERE name = ''CleanSysDatabaseLog''
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40282714
If you want to know which jobs are running at the moment just run the following query:
select job.name, act.start_execution_date, act.last_executed_step_date, act.last_executed_step_id, act.stop_execution_date
from msdb.dbo.sysjobs job
	inner join msdb.dbo.sysjobactivity act
	on job.job_id = act.job_id
where act.start_execution_date is not null
  and act.stop_execution_date is null

Open in new window

0
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1200 total points
ID: 40282715
AXISHK, you forgot to close the string (last quotation missing):
EXECUTE sp_MSForEachDB 
       'USE ?;
       SELECT DB_NAME();
     SELECT * FROM sys.procedures WHERE name = ''CleanSysDatabaseLog''' 

Open in new window

0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 400 total points
ID: 40282790
you can also use the free SQL Search: http://www.red-gate.com/products/sql-development/sql-search/
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40282831
>is there a quick way to check where does this stored procedure located ?
Just to state the obvious, it's not a good programming practice to tell SQL Server to 'go fish' for an object.
You'll want to explicitly state where it's at.
0
 

Author Closing Comment

by:AXISHK
ID: 40284792
Tks
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question