Script to CHECK Startup procedures in SQL Server

Written by VidhyaSagar. Posted in Scripts

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
VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.17_1161]
Rating: 0 (from 0 votes)

Tags: , , ,

Trackback from your site.

Comments (2)

  • Dan Moran

    |

    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.

    Reply

    • VidhyaSagar

      |

      Yep! thanks Dan

      Reply

Leave a comment

CAPTCHA Image
*

Recent Comments

RA

|

Thanks for the script. I had database with space between the names.

I modified to use square brackets:

FROM [?].sys.database_principals a
LEFT OUTER JOIN [?].sys.database_role_members

and

from [?].sys.database_permissions join [?].sys.sysusers U
on grantee_principal_id = uid join [?].sys.sysobjects

Gangadhar NG

|

This tip is helpful enough for me.
thanks.

Zukunftsmusik

|

Thanks! That helped my a lot. But there’s a little mistake:
SQL_Latin1_General_CP1_CI_AS stands for “Latin1-General, case-insensitive, accent-sensitive [...]” therefore sort order 52.

If you want to set the sort order to 54 the correct collation would be “SQL_Latin1_General_CP1_CI_AI” ;)

Amit Bhatt

|

Hi Deepak,

Thanks for such a nice article.

You missed one thing to add in code:
@article = ‘all’,

Hence the script will be like this:
EXEC sp_addsubscription
@publication = ‘mypublication’,
@article = ‘ALL’,
@subscriber = ‘Subscriberservername’,
@destination_db = ‘mydestinationdbname’,
@reserved=’Internal’

Error 18486 | Platformblog

|

[...] SQL-Articles » Troubleshooting Login failed Error 18456This is one of the infamous error message (and number) that most of the DBAs …. 18486. Login failed for user ‘%.*ls’ because the account is currently locked out. [...]

Rating