Skip to content

Instantly share code, notes, and snippets.

@dejanstojanovic
Last active August 29, 2015 14:16
Show Gist options
  • Save dejanstojanovic/a8696496f5cb73f8f810 to your computer and use it in GitHub Desktop.
Save dejanstojanovic/a8696496f5cb73f8f810 to your computer and use it in GitHub Desktop.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE MigrateStoredProcedure
@ProcedureName VARCHAR(50),
@TargetDatabase VARCHAR(50)
AS
BEGIN
DECLARE @GetCode CURSOR
DECLARE @Code NVARCHAR(MAX)
SET @GetCode = CURSOR FOR
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE @ProcedureName
OPEN @GetCode
FETCH NEXT
FROM @GetCode INTO @Code
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT
-- GET SP CODE
FROM @GetCode INTO @Code
-- PREPARE FOR EXECUTING IN TARGET DATABASE
SET @Code = @TargetDatabase + '.dbo.sp_executesql N' + CHAR(39) + @Code + CHAR(39)
--EXECUTE COMMAND
EXEC sp_executesql @Code
END
CLOSE @GetCode
DEALLOCATE @GetCode
END
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment