The below T-SQL generates a script for SP's which satisfies the search criteria.
Replace the @SearchFor parameter in the below SQL's and execute the code
-- set "Result to Text" mode by pressing Ctrl+T
SET
NOCOUNT
ON
DECLARE
@sqlToRun
VARCHAR
(1000), @searchFor
(100), @replaceWith
(100)
-- text to search for
@searchFor =
'line'
-- this will hold stored procedures text
@
temp
TABLE
(spText
(
MAX
))
curHelp
CURSOR
FAST_FORWARD
FOR
-- get text of all stored procedures that contain search string
-- I am using custom escape character here since i need to espape [ and ] in search string
SELECT
DISTINCT
'sp_helptext '
''
+OBJECT_SCHEMA_NAME(id)+
'.'
+OBJECT_NAME(id)+
' '
FROM
syscomments
WHERE
TEXT
LIKE
'%'
+
REPLACE
(@searchFor,
']'
,
'\]'
),
'['
'\['
) +
ESCAPE
'\'
ORDER BY '
sp_helptext
'+OBJECT_SCHEMA_NAME(id)+'
.
'+OBJECT_NAME(id)+'
'
OPEN curHelp
FETCH next FROM curHelp INTO @sqlToRun
WHILE @@FETCH_STATUS = 0
BEGIN
--insert stored procedure text into a temporary table
INSERT INTO @temp
EXEC (@sqlToRun)
-- add GO after each stored procedure
VALUES ('
GO')
FETCH
next
INTO
END
CLOSE
DEALLOCATE
spText
-- now copy and paste result into new window
-- then make sure everything looks good and run
GO
@Test
(Id
INT
IDENTITY(1,1), Code
varchar
max
@searchFor
'Line'
INSERT
@Test (Code)
'IF object_ID(N'
+ schema_name(schema_id) +
'].['
Name
') IS NOT NULL
DROP PROCEDURE ['
' ].['
char
(13) +
(10) +
'GO'
OBJECT_DEFINITION(OBJECT_ID) +
(10)
from
sys.procedures
where
is_ms_shipped = 0
and
OBJECT_DEFINITION(OBJECT_ID)
+@searchFor+
@lnCurrent
int
, @lnMax
@LongName
)
@lnMax =
(Id)
@lnCurrent = 1
WHILE @lnCurrent <= @lnMax
@LongName = Code
Id = @lnCurrent
WHILE @LongName <>
print
LEFT
(@LongName,8000)
@LongName =
SUBSTRING
(@LongName, 8001, LEN(@LongName))
@lnCurrent = @lnCurrent + 1
Replace the @SearchFor and @replacewith parameter in the below SQL's and execute the code. The output is copied into SSMS console and execute it to update all the SP's.
'[MY-SERVER]'
-- text to replace with
@replaceWith =
'[MY-SERVER2]'
')
CLOSE curHelp
DEALLOCATE curHelp
-- find and replace search string in stored procedures
-- also replace CREATE PROCEDURE with ALTER PROCEDURE
UPDATE @temp
SET spText = REPLACE(REPLACE(spText,'
CREATE
PROCEDURE
', '
ALTER
'),@searchFor,@replaceWith)
Step 3: In Server Options tab – put "Data Access", RPC, "Rpc Out" and "Use Remote Collaboration" to be true.