find the all the stored procedures and jobs in sql server 2000

Question

Hi,

In SQL SERVER 2005 This query works fine : Select * from sys.procedures where object_definition(object_id) like '%J%' SELECT * FROM MSDB.DBO.SYSJOBS WHERE NAME LIKE '%J%'

but in sql server 2000 it is not working. Here i need to find the all the stored procedures and jobs which matches my string ? how to find in sql server 2000 ?

regards, kumar

Answer 1

SQL 2000 lacks the sys.procedures system table - the closest you'll find is sysobjects, and you could do your select WHERE name=? AND XTYPE='P'.

To get SQL agent jobs, you can do this:

SELECT job_id, [name] FROM msdb.dbo.sysjobs WHERE NAME=?;

Answer 2

syscomments contains the text of procedure definitions (in both it's text and ctext columns).

However, you should be aware that large procedures can span multiple rows, so if what you're searching for in your LIKE condition is large, you may be unlucky and it will be split across a row boundary. There's no easy fix for this, since SQL Server 2000 doesn't have varchar(max).