Tuesday, December 20, 2016

Finding SQL Server Startup Stored Procedures

When I was discussing about getting Stored Procedures automatically executed at service start, a question raised; How can we find Stored Procedure added as Startup Procedures. This is simple, all we have to do is, query the sys.procedure system view and filter the result for ExecIsStartup Property.

Here is the code;

USE master;
GO

SELECT *
FROM sys.procedures
WHERE OBJECTPROPERTY(OBJECT_ID, 'ExecIsStartup') = 1;

For more info on how to add procedures as startup procedures using sp_procoption, read this: https://msdn.microsoft.com/en-us/library/ms181720.aspx

No comments: