This is pretty standard stuff. It's nice, but needs more options.

It does not work on SQL Server 7 (I know, but our company is cheap and doesn't want to upgrade some of our applications).

I wrote an ASP page that does the same, but it also determines if it is SQL 7 or higher, and there is an option to let you choose a DB or search all DB's on the server.

Yes, it's ugly, but it works. I might add more to it later. The lines that start with sqlCmd.CommandText are the ones containin the sql code.

You'll have to play with this a little to get it working in SQL server, b/c I've had to quote a lot of things to format it for use in an ASP(vb.net) page.

If (Database.Equals("")) Then 'Search All Databases

If (ver < 8) Then

sqlCmd.CommandText = "exec sp_MSForEachDB @command1='use [?];SELECT OBJECT_NAME(id) as ROUTINE_CATALOG, ''[?]'' as ROUTINE_NAME, '' '' as ROUTINE_TYPE, '' '' as CREATED FROM syscomments WHERE [text] LIKE ''%" + SearchString + "%'' AND OBJECTPROPERTY(id, ''IsProcedure'') = 1 GROUP BY OBJECT_NAME(id); '"

Else

sqlCmd.CommandText = "exec sp_MSForEachDB @command1='use [?];SELECT ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_TYPE, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ''%" + SearchString + "%'' AND (ROUTINE_TYPE=''PROCEDURE'' or ROUTINE_TYPE=''FUNCTION''); '"

End If

Else ' Search only the specified Database

If (ver < 8) Then

sqlCmd.CommandText = "use " + Database + ";SELECT OBJECT_NAME(id) as ROUTINE_CATALOG, '" + Database + "' as ROUTINE_NAME, ' ' as ROUTINE_TYPE, ' ' as CREATED FROM syscomments WHERE [text] LIKE '%" + SearchString + "%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id);"

Else

sqlCmd.CommandText = "use " + Database + ";SELECT ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_TYPE, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%" + SearchString + "%' AND (ROUTINE_TYPE='PROCEDURE' or ROUTINE_TYPE='FUNCTION');"

End If

End If