SET NOCOUNT ON ;

DECLARE @cmd varchar(max);
DECLARE @object_id int, @parameter_id int;
DECLARE @schema_name sysname, @object_name sysname ;
DECLARE @parameter_definition varchar(200)

DECLARE procedure_cursor CURSOR FOR 
SELECT s.name, o.name, o.object_id
FROM [<Linked Server Name, sysname, LinkedServer>].[<Remote DB Name, sysname, RemoteDB>].sys.procedures o
JOIN [<Linked Server Name, sysname, LinkedServer>].[<Remote DB Name, sysname, RemoteDB>].sys.schemas s ON s.schema_id = o.schema_id
WHERE o.SCHEMA_ID IN (
		SELECT SCHEMA_ID FROM [<Linked Server Name, sysname, LinkedServer>].[<Remote DB Name, sysname, RemoteDB>].sys.schemas WHERE name = ' <Remote Schema Name, sysname, RemoteSchema>' 
)

OPEN procedure_cursor

FETCH NEXT FROM procedure_cursor INTO @schema_name, @object_name, @object_id

WHILE @@FETCH_STATUS = 0
BEGIN

	set @cmd = 'CREATE PROCEDURE ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@object_name) + ' '

    DECLARE parameter_cursor CURSOR FOR 
    SELECT parameter_id, QUOTENAME(p.name) + ' ' + QUOTENAME(t.name) + CASE WHEN t.name IN ( 'decimal', 'numeric' ) THEN ' (' + CAST(p.[precision] AS nvarchar(4)) + ',' + CAST(p.[scale] AS nvarchar(4)) + ') '
WHEN t.name IN ( 'float', 'time', 'date', 'datetime2' ) THEN ' (' + CAST(p.[precision] AS nvarchar(4)) + ') '
WHEN t.name IN ( 'varbinary', 'varchar', 'char' ) THEN CASE p.max_length WHEN - 1 THEN '(max) ' ELSE ' (' + CAST(p.[max_length] AS nvarchar(4)) + ') ' END
WHEN t.name IN ( 'nchar', 'nvarchar' ) THEN CASE p.max_length WHEN - 1 THEN ' (max) ' ELSE ' (' + CAST(p.[max_length]/2 AS nvarchar(4)) + ') ' END
ELSE ' '
END + CASE p.is_output  WHEN 1 THEN ' OUTPUT' ELSE CASE p.is_readonly  WHEN 1 THEN ' READONLY' ELSE ' = NULL ' END END + CASE WHEN parameter_id > 1 THEN ','
	ELSE ''
	END
	FROM [<Linked Server Name, sysname, LinkedServer>].[<Remote DB Name, sysname, RemoteDB>].sys.parameters p
	JOIN [<Linked Server Name, sysname, LinkedServer>].[<Remote DB Name, sysname, RemoteDB>].sys.types t ON t.user_type_id = p.user_type_id
    WHERE p.object_id = @object_id
    ORDER BY p.parameter_id

    OPEN parameter_cursor
    FETCH NEXT FROM parameter_cursor INTO @parameter_id, @parameter_definition 

    WHILE @@FETCH_STATUS = 0
    BEGIN
		SET @cmd += @parameter_definition 
		
		FETCH NEXT FROM parameter_cursor INTO @parameter_id, @parameter_definition 
	END

    CLOSE parameter_cursor
    DEALLOCATE parameter_cursor
    -- Remove trailing comma
    IF RIGHT(@cmd,1) = ',' 
		SET @cmd = LEFT(@cmd,datalength(@cmd) - 1 )
	SET @cmd += '
AS
BEGIN
	SET NOCOUNT ON ;
	EXEC [<Linked Server Name, sysname, LinkedServer>].[<Remote DB Name, sysname, RemoteDB>].' + + QUOTENAME(@schema_name) + '.' + QUOTENAME(@object_name) + ' ;
END
GO'

	PRINT @cmd ;

    FETCH NEXT FROM procedure_cursor INTO @schema_name, @object_name, @object_id
END 
CLOSE procedure_cursor
DEALLOCATE procedure_cursor