SQL Server: How to backup and replace text in all search string matching Stored Procedure(s)

Article
1/17/2024

Problem Statement:

After SQL Server Migration or database rename or object rename, the dependent objects such as SP’s, linked servers requires modification. Is there a best way to update names in all the stored procedure 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 an alias.

The step by step details are given below

Generate script of all stored procedures - You can use the scripting wizard 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 a 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 a provider.
Add sql_server in "Product Name" field (that's the magic).
In "Data Source" – specify a name of the host to be used as a 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. It's 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 an efficient way to rollback as it generates script for all eligible SP's

Share via