Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stonebreaker
Yak Posting Veteran
92 Posts |
 Posted - 2009-12-16 : 13:45:59
|
| As the title says, I need to find out if any jobs, maintenance plans, reports, or ssis packages use a certain stored proc. We are cleaning up the database, and want to know if it is OK to delete.Thanks,StonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2009-12-16 : 16:27:08
|
I have never done this, but I would start by looking at the system tables for each one. I just did google search's for the following terms and this is what I found.Query Sql Jobs - http://sqlserverpedia.com/wiki/SQL_Server_Agent_Job_Query_SamplesQuery Sql Maintenance Plans - http://www.mssqltips.com/tip.asp?tip=1137QUery SQL SSIS packages - I think you're going to need to disect from looking at the packagesYou should be able to hopfully piece it apart, but like I said this is just how I would approach, I have never needed to do this prior.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
 |
|
|
stonebreaker
Yak Posting Veteran
92 Posts |
Posted - 2009-12-16 : 16:53:23
|
| Thanks for the tip on the msdb tables - I was able to find a couple, dbo.sysjobs and dbo.sysjobsteps, that will let me find what I need for jobs. I'll keep researching the rest.select sj.name,sjs.step_id,sjs.step_name,sjs.commandfrom dbo.sysjobs sjjoin dbo.sysjobsteps sjs on sj.job_id = sjs.job_idwhere command like '%search_condition%'StonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin |
|
|
|
|
|
|
|
|
Need to query jobs and reports for stored proc