Find Startup procedures in an instance

Yesterday one of the customers wanted to know if one of their startup stored procedures was still active on a Production Instance. This information is easy to get. Since I had not mentioned about it in my blog so far, here it is now. This script will list all the startup stored procedures which are enabled on a given instance.

On SQL Server 2000

SELECT name
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1

On SQL Server 2005 and above

SELECT name
FROM sys.objects
WHERE type = 'P'
AND OBJECTPROPERTY(object_id, 'ExecIsStartup') = 1

Some information about Startup Procedures:

  • The startup procedure must be in the master database
  • SA login should be the owner of those procedures
  • These procedures will be executed after the master database has been recovered during startup
  • Trace flag 4022 can be used to disable automatic execution of startup stored procedures

2 thoughts on “Find Startup procedures in an instance

  1. $sammy!

    cool! Like to know if we can find out a database name just by having a table .. example i have table but im not sure to which DB it belongs n which file group.

Comments are closed.