How to Change Linked Server Name in Stored Procedures - Netwoven

How to Change Linked Server Name in Stored Procedures

By Somnath Saha  |  Published on February 6, 2018

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.


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

2. Connect master database.

3. Script:

Use <restored database name>
DECLARE @searchFor VARCHAR(100), @replaceWith VARCHAR(100
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( as SPName
from syscomments c, sysobjects o 
where (c.text like '%' + @ searchFor + '%' and c.text not like '%' + @replaceWith + '%')
and =
and o.type = 'P'

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


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.

Leave a comment

Your email address will not be published. Required fields are marked *

Unravel The Complex
Stay Connected

Subscribe and receive the latest insights

Netwoven Inc. - Microsoft Solutions Partner

Get involved by tagging Netwoven experiences using our official hashtag #UnravelTheComplex