How To Backup And Replace Text In All Search String Matching Stored Procedure(S)

Problem Statement

After SQL Server Migration or database rename or object rename, the dependent objects such as SP’s,linked Servers require modification. Is there a best way to update names in all the stored procedures instead of manually checking and updating? Or how do we handle linked servers; by creating an alias?. How do we ensure and validate the SP’s or Can we take a backup of those procedures out of ‘n’ of SP’s?

Solution

Yes, We can take a backup of those SP's where it requires modification also for linked servers we can create alias.

The step by step details are given below

Generate script of all stored procedures - You can use the scripting wizrd to generate the script. Right-click the db –> tasks –> Generate scripts –> go through the wizard. The requirement is to generate for specific SP's where it meets the search string pre-requisite.
Generate an updated script - The same script is used to update all the eligible SP's with replace function.
Create alias for linked servers

Generate script for matching search string of all SP's

The below T-SQL generates script for SP's which satisfies the search criteria.

Using sp_helptext

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 VARCHAR(100), @replaceWith VARCHAR(100)
-- text to search for
SET @searchFor = 'line'
-- this will hold stored procedures text
DECLARE @temp TABLE (spText VARCHAR(MAX))
DECLARE 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(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
INSERT INTO @temp
VALUES ('GO')
FETCH next FROM curHelp INTO @sqlToRun
END
CLOSE curHelp
DEALLOCATE curHelp
SELECT spText FROM @temp
-- now copy and paste result into new window
-- then make sure everything looks good and run
GO

Using system view sys.procedures

Replace the @SearchFor parameter in the below SQL's and execute the code,

SET NOCOUNT ON
DECLARE @Test TABLE (Id INT IDENTITY(1,1), Code varchar(max))
DECLARE @searchFor VARCHAR(100)
SET @searchFor = 'Line'
INSERT INTO @Test (Code)
SELECT 'IF object_ID(N''[' + schema_name(schema_id) + '].[' + Name + ']'') IS NOT NULL
DROP PROCEDURE ['+ schema_name(schema_id) +' ].[' + Name + ']' + char(13) + char(10) + 'GO' + char(13) +char(10) +
OBJECT_DEFINITION(OBJECT_ID) + char(13) +char(10) + 'GO' + char(13) + char(10)
from sys.procedures
where is_ms_shipped = 0 and OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@searchFor+'%'
DECLARE @lnCurrent int, @lnMax int
DECLARE @LongName varchar(max)
SELECT @lnMax = MAX(Id) FROM @Test
SET @lnCurrent = 1
WHILE @lnCurrent <= @lnMax
BEGIN
SELECT @LongName = Code FROM @Test WHERE Id = @lnCurrent
WHILE @LongName <> ''
BEGIN
print LEFT(@LongName,8000)
SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName))
END
SET @lnCurrent = @lnCurrent + 1
END

Generate modified SP's script

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.

-- set "Result to Text" mode by pressing Ctrl+T
SET NOCOUNT ON
DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)
-- text to search for
SET @searchFor = '[MY-SERVER]'
-- text to replace with
SET @replaceWith = '[MY-SERVER2]'
-- this will hold stored procedures text
DECLARE @temp TABLE (spText VARCHAR(MAX))
DECLARE 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(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
INSERT INTO @temp
VALUES ('GO')
FETCH next FROM curHelp INTO @sqlToRun
END
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 PROCEDURE'),@searchFor,@replaceWith)
SELECT spText FROM @temp
-- now copy and paste result into new window
-- then make sure everything looks good and run
GO

Create Linked Server Alias

Step 1

In SQL Server Management Studio, open Linked Servers and then 'New Linked Server'.
Inside of appeared wizard, select the General tab.
Specify alias name in "Linked server" field.
Select SQL Native Client as provider.
Add sql_server in "Product Name" field (that's the magic).
In "Data Source", specify name of the host to be used as linked server.

Step 2

In Security tab, specify proper security options (e.g. security context).

Step 3

In Server Options tab, put "Data Access", RPC, "Rpc Out" and "Use Remote Collaboration" to be true.

Conclusion

Time Saving - Identifying and modifying many objects is going to be a tedious job. The script makes life easier. Migration is part of the evolution but think of updating SP's, Its really important to have a backup and easy if some automation like this able to modify what is needed
Easy to run and generate scripts for SP's based on search string
Easy to keep track of modified SP's and efficient way to rollback as it generates script for all eligible SP's

References

http://www.ideosity.com/ourblog/post/ideosphere-blog/2013/06/14/how-to-find-and-replace-text-in-all-stored-procedures
http://alexpinsker.blogspot.com/2007/08/how-to-give-alias-to-sql-linked-server.html
http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-script-all-stored-procedures-in-a/