Copy Stored Procedures Between Servers

This procedure will migrate all your stored procedures located on on server to another via linked server. Just specify the linked server name, remote database, and local database.

[cc lang=”sql”]

CREATE PROCEDURE [dba].[pr_refresh_create_procedures]
@linked_server varchar(100),
@source_db varchar(100),
@target_db varchar(100)
AS
–EXEC [dba].[pr_refresh_create_procedures] ‘LOCALSERVER’, ‘yourdbname’, ‘targetdb_dev’
SET @source_db = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’;

DECLARE @sql Nvarchar(max)
DECLARE @Name varchar(255) = @target_db

IF OBJECT_ID(N’admindb.tmp.shelldb_copy_procedures’) IS NOT NULL DROP TABLE admindb.tmp.shelldb_copy_procedures

SET @sql =
‘SELECT p.name, m.Definition
INTO admindb.tmp.shelldb_copy_procedures
FROM ‘ + @source_db + ‘.sys.objects p WITH (NOLOCK)
INNER JOIN ‘ + @source_db + ‘.sys.sql_modules m WITH (NOLOCK) ON p.object_id = m.object_id
WHERE type IN (”FN”, ”IF”, ”TF”, ”P”, ”V”, ”TT”)’

exec(@sql)

DECLARE c CURSOR FOR
SELECT Definition
FROM admindb.tmp.shelldb_copy_procedures

OPEN c

FETCH NEXT FROM c INTO @sql

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = REPLACE(@sql,””,”””)
set @sql = N’execute ‘ + QUOTENAME(@name) + N’.dbo.sp_executesql N”’ + @sql + ””

EXEC(@sql)

FETCH NEXT FROM c INTO @sql
END

CLOSE c
DEALLOCATE c

[/cc]