0 Replies - 1484 Views - Last Post: 10 June 2008 - 01:04 AM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1660
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Copy stored procedures from one server to another with SQL 2005

Posted 10 June 2008 - 01:04 AM

Description: Must be running SQL 2005 Call PromoteFromDev passing it a stored procedure name, it will then call the user defined function used to get the actual script of the stored procedure.Here is a snippet (1 Stored Procedure, 1 User Defined Function) for copying stored procedures between databases
/*
Stored Procedure for copying procedures
from one database to another
*/
CREATE PROCEDURE PromoteFromDev @ProcedureName VARCHAR(100)
AS
BEGIN
DECLARE @Proc VARCHAR(MAX)
IF @ProcedureName IS NOT NULL
    --Replace YourDatabase with the name of the db this function is installed on
    SELECT @Proc = YourDatabase.dbo.udfGetProcedureScript(@ProcedureName)
IF @Proc IS NOT NULL
    EXEC @Proc
END

/*
User Defined Function used to retrieve the script
of the selected stored procedure. This can be created on any database, 
you just need to reference it when calling it
*/
CREATE FUNCTION dbo.udfGetProcedureScript(@ProcedureName VARCHAR(100))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Proc VARCHAR(MAX)
    SELECT 
		@Proc = REPLACE((SELECT SysComments.Text AS "data()"
    FROM 
		SysComments INNER JOIN Sysobjects 
		 ON SysObjects.ID = SysComments.ID 
	WHERE 
		Sysobjects.name = @ProcedureName FOR XML PATH('')),'
','')
    RETURN @Proc
END


Is This A Good Question/Topic? 0
  • +

Page 1 of 1