Microsoft's sp_grep system stored procedure improved

Here is a quick patch to Microsoft’s sp_grep system stored procedure, for SQL Server 2000. The changes will enable searching job steps, and return the correct object type for functions. The MS-supplied stored procedure isn’t that well-written (doesn’t even use ANSI joins), but this patch doesn’t correct any style or other issues.

> insert into #tset
>   select distinct '',, 'JB' from msdb..sysjobs as j(nolock)
>     inner join msdb..sysjobsteps as js(nolock) on j.job_id = js.job_id
>   where js.command like @str or like @str
>   when type = 'FN' then 'Function'
>   when type = 'JB' then 'Job'

I have seen variations in this sproc to prevent grepping where the user has no access to the database, viz:

if HAS_DBACCESS(@name) = 1
    print 'Skipped ' + @name

I don’t think this is safe. If the user doesn’t have access to the database, I think it’s better to die, instead of lying by omission about the results (in case the user isn’t viewing the results in text mode, and doesn’t see the message printed out). However, it’s a fine idea to add an extra entry in #tset, add a case to the case statement at the end, and show the user something like Denied! Access denied to DB 'master', dude!.

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.