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/