Post
Ask Question

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,

  1. -- set "Result to Text" mode by pressing Ctrl+T  
  2. SET NOCOUNT ON  
  3. DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)  
  4. -- text to search for  
  5. SET @searchFor = 'line'  
  6. -- this will hold stored procedures text  
  7. DECLARE @temp TABLE (spText VARCHAR(MAX))  
  8. DECLARE curHelp CURSOR FAST_FORWARD  
  9. FOR  
  10. -- get text of all stored procedures that contain search string  
  11. -- I am using custom escape character here since i need to espape [ and ] in search string  
  12. SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '  
  13. FROM syscomments WHERE TEXT LIKE '%' + REPLACE(REPLACE(@searchFor,']','\]'),'[','\[') + '%' ESCAPE '\'  
  14. ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '  
  15. OPEN curHelp  
  16. FETCH next FROM curHelp INTO @sqlToRun  
  17. WHILE @@FETCH_STATUS = 0  
  18. BEGIN  
  19. --insert stored procedure text into a temporary table  
  20. INSERT INTO @temp  
  21. EXEC (@sqlToRun)  
  22. -- add GO after each stored procedure  
  23. INSERT INTO @temp  
  24. VALUES ('GO')  
  25. FETCH next FROM curHelp INTO @sqlToRun  
  26. END  
  27. CLOSE curHelp  
  28. DEALLOCATE curHelp  
  29. SELECT spText FROM @temp  
  30. -- now copy and paste result into new window  
  31. -- then make sure everything looks good and run  
  32. GO   

Using system view sys.procedures

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

  1. SET NOCOUNT ON  
  2. DECLARE @Test TABLE (Id INT IDENTITY(1,1), Code varchar(max))  
  3. DECLARE @searchFor VARCHAR(100)  
  4. SET @searchFor = 'Line'  
  5. INSERT INTO @Test (Code)  
  6. SELECT 'IF object_ID(N''[' + schema_name(schema_id) + '].[' + Name + ']'') IS NOT NULL  
  7. DROP PROCEDURE ['+ schema_name(schema_id) +' ].[' + Name + ']' + char(13) + char(10) + 'GO' + char(13) +char(10) +  
  8. OBJECT_DEFINITION(OBJECT_ID) + char(13) +char(10) + 'GO' + char(13) + char(10)  
  9. from sys.procedures  
  10. where is_ms_shipped = 0 and OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@searchFor+'%'  
  11. DECLARE @lnCurrent int, @lnMax int  
  12. DECLARE @LongName varchar(max)  
  13. SELECT @lnMax = MAX(Id) FROM @Test  
  14. SET @lnCurrent = 1  
  15. WHILE @lnCurrent <= @lnMax  
  16. BEGIN  
  17. SELECT @LongName = Code FROM @Test WHERE Id = @lnCurrent  
  18. WHILE @LongName <> ''  
  19. BEGIN  
  20. print LEFT(@LongName,8000)  
  21. SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName))  
  22. END  
  23. SET @lnCurrent = @lnCurrent + 1  
  24. 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.

  1. -- set "Result to Text" mode by pressing Ctrl+T  
  2. SET NOCOUNT ON  
  3. DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)  
  4. -- text to search for  
  5. SET @searchFor = '[MY-SERVER]'  
  6. -- text to replace with  
  7. SET @replaceWith = '[MY-SERVER2]'  
  8. -- this will hold stored procedures text  
  9. DECLARE @temp TABLE (spText VARCHAR(MAX))  
  10. DECLARE curHelp CURSOR FAST_FORWARD  
  11. FOR  
  12. -- get text of all stored procedures that contain search string  
  13. -- I am using custom escape character here since i need to espape [ and ] in search string  
  14. SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '  
  15. FROM syscomments WHERE TEXT LIKE '%' + REPLACE(REPLACE(@searchFor,']','\]'),'[','\[') + '%' ESCAPE '\'  
  16. ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '  
  17. OPEN curHelp  
  18. FETCH next FROM curHelp INTO @sqlToRun  
  19. WHILE @@FETCH_STATUS = 0  
  20. BEGIN  
  21. --insert stored procedure text into a temporary table  
  22. INSERT INTO @temp  
  23. EXEC (@sqlToRun)  
  24. -- add GO after each stored procedure  
  25. INSERT INTO @temp  
  26. VALUES ('GO')  
  27. FETCH next FROM curHelp INTO @sqlToRun  
  28. END  
  29. CLOSE curHelp  
  30. DEALLOCATE curHelp  
  31. -- find and replace search string in stored procedures  
  32. -- also replace CREATE PROCEDURE with ALTER PROCEDURE  
  33. UPDATE @temp  
  34. SET spText = REPLACE(REPLACE(spText,'CREATE PROCEDURE', 'ALTER PROCEDURE'),@searchFor,@replaceWith)  
  35. SELECT spText FROM @temp  
  36. -- now copy and paste result into new window  
  37. -- then make sure everything looks good and run  
  38. 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/