Good for creating the update scripts
select '/************' + OBJECT_NAME(id) + '************/' + CHAR(13) + [text] + CHAR(13)
FROM
syscomments
WHERE
OBJECT_NAME(id) in
(
SELECT routine_name
FROM information_schema.routines
WHERE last_altered > '2008-01-09' /****Your conditions go here********/
AND [text] LIKE '%blah%'
AND routine_type = 'PROCEDURE'
)
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
Create 'drop and create' procedure script using the pattern
select '/************' + OBJECT_NAME(id) + '************/' + CHAR(13) +
'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + OBJECT_NAME(id) + ']'') AND type in (N''P'', N''PC''))
DROP PROCEDURE [dbo].[' + OBJECT_NAME(id) + ']' + CHAR(13) + 'GO' + CHAR(13) +
[text] + CHAR(13) + 'GO' + CHAR(13)
from syscomments
where OBJECT_NAME(id) in
(
SELECT routine_name
FROM information_schema.routines
WHERE last_altered > '2007-05-09'
AND routine_type = 'PROCEDURE'
)
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
