XML FeedsYou know that you can mark a proc to run at startup in SQL Server. What you have to do is create the stored procedure in the master database and after that you have to set the startup flag to true. If your stored procedure name is spMyProc, the code would look like this.
- exec sp_procoption N'spMyProc', 'startup', 'on'
What if you want to list all the stored procedures on your server which are set to run on startup? Here is how you do this
- SELECT name,create_date,modify_date
- FROM sys.procedures
- WHERE OBJECTPROPERTY(OBJECT_ID, 'ExecIsStartup') = 1
That query will give you the name, creation date and modification date of every stored procedure that is set to run on startup.
Aaron Bertrand pointed out that you can also use the is_auto_executed column, here is what the query would look like now, much easier to read
- SELECT name,create_date,modify_date
- FROM sys.procedures
- WHERE is_auto_executed = 1
Creating stored procedures to run on startup is a nice way of ensuring that certain flags/tables/users are created/updated/deleted when the SQL instance is restarted, this way you won't forget to run that code that you have tucked away in some folder that needs to be run to initialize some data on startup.
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Why use objectproperty() when there is already a column in sys.procedures for this? Not that it's a performance concern I just find column references easier to read than nested built-in function calls... 
Thanks Aaron, I added the query you suggested also
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.