What startup stored procedures are we running?

For some environments certain actions must be taken when SQL Server starts up. For some customers temporary tables or other types of transient objects must be present in the Tempdb. Developers or DBAs will then create "start up stored procedures" to ensure that when SQL Server starts up these procedures will execute.

What happens if the original developers/DBAs leave and there is no documentation? How can we find out which objects are being executed at startup? I recently had this question so I thought I would post the answer here as it is interesting.

SELECT ObjectProperty(object_id,'ExecIsStartup'),*

FROM sys.objects

WHERE TYPE IN('X','P','PC')

AND ObjectProperty(object_id,'ExecIsStartup') = 1

regards,

Norman