after executing this script: matching the findtext sp is dropped and i cannot find the SP in the database !! ideally updated SP should be there...
not working for me.
Declare @spnames CURSOR
Declare @spname nvarchar(max)
Declare @moddef nvarchar(max)
Set @spnames = CURSOR FOR
select distinct object_name(c.id)
from syscomments c, sysobjects o
where c.text like '%findtext%'
and c.id = o.id
and o.type = 'P'
OPEN @spnames
FETCH NEXT
FROM @spnames into @spname
WHILE @@FETCH_STATUS = 0
BEGIN
Set @moddef =
(SELECT
Replace ((REPLACE(definition,'findtext','replacetext')),'ALTER','create')
FROM sys.sql_modules a
JOIN
( select type, name,object_id
from sys.objects b
where type in (
'p' -- procedures
)
and is_ms_shipped = 0
)b
ON a.object_id=b.object_id where b.name = @spname)
exec('drop procedure dbo.' + @spname)
execute sp_executesql @moddef
FETCH NEXT FROM @spnames into @spname
END