How to Change Linked Server Name in Stored Procedures

When we restore on-premise SQL database from one environment to another environment, we may need to change linked server names which exist in the inside of source database’s stored procedures to new linked server name in the restored database. For example, if the source database name is “NetwovenDatabase” in on-premise SQL server and linked server name associated with “NetwovenDatabase” is “LinkedServer1” then after restoration of “NetwovenDatabase” to another on-premise SQL server, we need to change the associated linked server name from “LinkedServer1” to “LinkedServer2”.

We can change linked server name from “LinkedServer1” to “LinkedServer2” manually by opening procedures one by one but this is not an efficient way to do that. Hence, to do that in an efficient way we need to execute following scripts after restoring the database from one environment to another environment.

Steps:

1. Connect restored database server using SSMS with administrative privilege.

2. Connect master database.

3. Script:

Use <restored database name>
SET NOCOUNT ON  
DECLARE @searchFor VARCHAR(100), @replaceWith VARCHAR(100
DECLARE @count INT 
DECLARE @i INT =1 
DECLARE @SPName VARCHAR(1000)
Declare @moddef nvarchar(max)
--Declare Table variables for storing data 
DECLARE @TStoredProcedures TABLE ( SNo INT IDENTITY(1,1),
 SPName varchar(max)
 ) 
-- text to search for  
SET @searchFor = '% LinkedServer1%'
-- text to replace with  
SET @replaceWith = ' LinkedServer2’

INSERT INTO @TStoredProcedures(SPName)
select distinct object_name(c.id) as SPName
from syscomments c, sysobjects o 
where (c.text like '%' + @ searchFor + '%' and c.text not like '%' + @replaceWith + '%')
and c.id = o.id
and o.type = 'P'


SELECT @count = COUNT(SNo) FROM @TStoredProcedures WHILE (@i <= @count)
 BEGIN
	SELECT @SPName = SPName FROM @TStoredProcedures WHERE SNo = @i
	Set @moddef =
    (SELECT
    Replace (REPLACE(definition,@searchFor,@replaceWith) ,'create ','ALTER ')
    FROM sys.sql_modules a
    JOIN 
    (   select type, name,object_id
    from sys.objects b
    where type in (
    'p' -- procedures
    )
    and is_ms_shipped = 0
    )b
    ON a.object_id=b.object_id where b.name = @SPName)
    --exec('drop procedure dbo.' + @spname)
    execute sp_executesql @moddef
	--select @SPName
    SELECT @i = @i + 1
 END

Conclusion:

Using this script, you can change the linked server name which exists in the inside of restored database’s stored procedures. Hope you will find it useful.