/*Script to CHECK Startup procedure in SQL Server
DATE : 14-August-2008
Author : Vidhya Sagar
http://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