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

Table of Contents

• Problem Statement:-
• Solution:-
• Generate script for matching search string of all SP’s

  Using sp_helptext

• Using system view sys.procedures
• Generate modified SP’s script
• Create Linked Server Alias
• Conclusion
• References

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 updated 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 the 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 the 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, 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 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/