startup procedures

[bdiaz]

Can you tell me how to find out whihc procedures are run at SQL Server startup (those procedures whihc have been marked as autoproc using sp_procoption) ?

 

[Catadmin]

Try running this in the database in question:

Select sobj.*, sprop.*
from  sysobjects sobj
left outer join sysproperties sprop
on sprop.ID = sobj.ID
where sobj.XType = 'P'

If I am correct (someone correct me if I'm not), Value from sysproperties should be the value of the sp_procoption you used. 'P' should stand for stored procedure.

Hope this helps.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[bdiaz]

I think it isn't, as I have no row at all on sysproperties table.

 

[Catadmin]

Okay. Let me find my system tables diagram and see if I can figure it out.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[Catadmin]

So far the only thing I can find is master.dbo.sysconfigures which shows that the config 1547 is "Scan for Startup" with stored procedures. I haven't yet found how to connect this table back to the sysobjects table in a user database.

Thought I would post what I did find though, in case someone can use it to come up with a solution before I do.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[SQLDenis]

select name from sysobjects where xtype = 'p' and OBJECTPROPERTY(id, 'ExecIsStartup') = 1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/