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