Have an application with 100′s of stored procs and need to find one?  We use this code below that searchs all the stored proc’s syntax in a SQL database.

Create PROCEDURE [dbo].[Find_Text_In_SP]
@StringToSearch varchar(100)
AS
   SET @StringToSearch = '%' +@StringToSearch + '%'
   SELECT Distinct SO.Name
   FROM sysobjects SO (NOLOCK)
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
   AND SO.Type = 'P'
   AND SC.Text LIKE @stringtosearch
   ORDER BY SO.Name

Below is a script that I found that makes it very easy to set execute permissions to all all stored procs in a SQL Server 2005/2008 DB.

-- Set Username
DECLARE @U  sysname ; set @U = QUOTENAME('DB_USERNAME_HERE')
-- Set DB
DECLARE @DB  sysname ; set @DB = 'DB_NAME'
-- SET Schema
DECLARE @schema  sysname ; set @schema = 'SCHEMA_NAME'


DECLARE @ID           integer,
@LAST_ID     integer,
@NAME        varchar(1000),
@SQL         varchar(4000)

SET @LAST_ID = 0

WHILE @LAST_ID IS NOT NULL
BEGIN
SELECT @ID = MIN(id)
FROM dbo.sysobjects
WHERE id > @LAST_ID  AND type = 'P' AND category = 0

SET @LAST_ID = @ID

-- We have a record so go get the name
IF @ID IS NOT NULL
BEGIN
SELECT @NAME = name
FROM dbo.sysobjects
WHERE id = @ID

-- Build the DCL to do the GRANT
SET @SQL = 'GRANT EXECUTE ON ' +@DB + '.' + @schema +'.' + @NAME + ' TO ' + @U

PRINT 'setting execute permissions for: '+  @SQL
-- Run the SQL Statement you just generated
EXECUTE(@SQL)

END


END