Script to CHECK Startup procedures in SQL Server

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

Leave a Reply

2 responses to “Script to CHECK Startup procedures in SQL Server”

  1. SELECT ID, Name, [Type]
    FROM master.dbo.sysobjects
    WHERE TYPE IN (‘P’,’X’) AND Objectproperty(ID,’ExecIsStartup’) = 1

    — Same result, no cursor. Cursors are evil.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

´╗┐
x

Related Posts

Backup Report
I was working on one the project and they were looking for a backup report which should specify whether it's physical or logical and few more det...
T-SQL Script to find the names of the StoredProcedure that has used dynamic SQL
This article has the script that will be useful to find the names of the  Stored procedure that has used dynamic sql within the definition of the...
Retrieve Auditing Configuration Details
Script to retrieve auditing configuration details in sql server. Auditing feature is released from SQL Server 2008 onward so if you have utilized...
powered by RelatedPosts