Job System Table

Hello,
Three questions:
(1) Is there a table (a system table?) on sql server 2000 that will
give you a list of the jobs on the database and when they are
scheduled? Also,
(2) Is there a table on SQL server 2000 that will give you the list of
DTS packages?
(3) Does SQL server track dependencies between jobs and stored
procedures? That is does it store a reference to objects that a stored
procedure or DTS package uses? If so is this information available in
a table?
Thanks for the helpBonifide,
1 and 2 - yes. Download the following...it will help answer these questions
and maybe more.
SQL Server System Table Map
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.mspx
HTH
Jerry
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegroups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>|||Look in the MSDB DB for sysjobs, sysjobsschedules, sysjobsteps, sysdtspackages
http://sqlservercode.blogspot.com/
"bonifide" wrote:
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>|||Comments Inline
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegroups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
There are multiple tables in msdb that together contain this information.
For jobs\databases, you will need to link sysjobsteps (for the database
information) and sysjobs. You will have to add sysjobservers if you are
using multi-server job administration. See the SQL 2000 System table map
for details.
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
msdb.dbo.sysdtspackages
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
No. SQL Server 7.0 and higher use late bindings so you can create objects
that refer to other objects that may not exist yet. There are some semsible
exceptions such as you cannot create a trigger on a table that does not
exist, but job steps can refer to nonexistent objects indefinitely.
> Thanks for the help
>
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP