A list of Auto exec SPs

Hello All,
A SP can be made to automatically execute when SQLServer restarts using the
store procedure "sp_procoption".
But is there a way to find out the list of SPs that have been congifured to
execute automatically. I took over as a DBA for an existing system and I was
wondering if there are any SP configured this way.
Thanks,
rgnIf you look at the definition for sp_procoption, you will discover the
following line of code:
UPDATE sysobjects SET status = (status & ~2) | (2 * @.intOptionValue) WHERE
id = @.tabid
This tells you, along with the rest of the definition, that if you query the
master.dbo.sysobjects table for status values of 2 on xtypes of X or P you
will find you startup procs and extended procs.
Sincerely,
Anthony Thomas
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
to
> execute automatically. I took over as a DBA for an existing system and I
was
> wondering if there are any SP configured this way.
> Thanks,
> rgn|||Hi rgn
You can use the OBJECTPROPERTY function.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartup') =1
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
> the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
> to
> execute automatically. I took over as a DBA for an existing system and I
> was
> wondering if there are any SP configured this way.
> Thanks,
> rgn