Script to CHECK Startup procedures in SQL Server

Today while checking a server for SQL Server startup performance issue, Im in thought of listing the startup procedures and to analyze those procedures for any issues. But when I Googled & I couldn’t find any script to list out the startup procedures, hence Ive written a script to list the startup procedures. Im sharing the same with you all. You can get the script from below link.

Just for your knowledge, SQL Server startup procedures will exists only in master database. You cant enable startup option for a procedure which resides in any other database other than master db.

Applies to:

SQL Server 2000
SQL Server 2005
SQL Server 2008

Script

/*Script to CHECK Startup procedure in SQL Server
DATE : 14-August-2008
Author : Vidhya Sagar
www.sql-articles.com
*/
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
DECLARE @procid INT
CREATE TABLE #tmp(objectname varchar(100),startup_status varchar(50))
DECLARE proc_cursor CURSOR FOR SELECT id FROM master.dbo.sysobjects WHERE TYPE IN ('P','X')
OPEN proc_cursor 
FETCH NEXT FROM proc_cursor INTO @procid
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #tmp  
SELECT OBJECT_NAME(@procid),CASE Objectproperty(@procid,'ExecIsStartup') WHEN 1 then 'Enabled' else 'Disabled' end
FETCH NEXT FROM proc_cursor INTO @procid
END
CLOSE proc_cursor
DEALLOCATE proc_cursor
SELECT * FROM #tmp WHERE startup_status = 'Enabled'
DROP TABLE #tmp