List and configure SQL Server startup stored procedures
How to flag a stored procedure to run when SQL Server starts with sp_procoption, and how to list these procedures.
A stored procedure flagged as a startup procedure runs automatically when the SQL Server instance starts. The flag is set with sp_procoption.
Syntax
sp_procoption [ @ProcName = ] 'procedure'
, [ @OptionName = ] 'option'
, [ @OptionValue = ] 'value'
The values of @OptionName and @OptionValue for the startup option are described in the sp_procoption documentation.
List the startup procedures
select * from sysobjects where (OBJECTPROPERTY(id, N'ExecIsStartup') = 1)
select name from sysobjects where xtype = 'p' and OBJECTPROPERTY(id, 'ExecIsStartup') = 1
Notes
sysobjectsis a compatibility view. On recent versions,sys.objectsis preferred:OBJECTPROPERTYaccepts the same object id.