how to copy proc from one db to another

  • Is it possible to PROGRAMATICALLY copy a stored procedure from one database to another?

    Thanks in advance,

    Billy

    Edited by - bp on 02/26/2003 11:37:55 AM

  • Billy

    I think there are several ways. One would be to go through Import wizard, create a job from it and call that job programmatically.

    Another way is to use sp_helptext to get source code of the procedure and execute it on another server.

    The most elegant way is probably to use SQL-DMO.

    Etc.

    Michael

  • Thanks Michael.

    The sp_helptext method is not an option because the proc is over 8000 chars long (the entire create proc script will not fit in the varchar(8000) I declare for the EXEC statement).

    I will look into the sql-dmo option.

    Billy

  • You could consider creating a DTS Package which transfered the Stored Procedure(s) with a Transfer SQL Objects task. The DTS Package could then be programmatically called with xp_cmdshell.

  • I always script my procs in text files and use ISQL to load/update them in one or more DB's. This way I never have to copy procs and also have documentation / backup.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • I do the same thing as David Burrows describes. I script all tables, vies, indexes, triggers, procedures, UDF, UDT, etc, etc. and store it all in VSS.

  • The source of code : http://www.SQLServerCentral.com

    /*******************************************************************************

    Written By : Simon Sabin

    Date : 25 October 2002

    Description : Copies a stored procedure from one database to another

    :

    History

    Date Change

    ------------------------------------------------------------------------------

    25/10/2002 Created

    *******************************************************************************/

    USE master

    GO

    IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name = 'sp_CopyStoredprocedure')

    DROP PROCEDURE sp_CopyStoredprocedure

    GO

    CREATE PROCEDURE dbo.sp_CopyStoredprocedure

    (

    @procedure sysname

    ,@sourceDb sysname

    ,@targetdb sysname

    )

    AS

    DECLARE @lines int

    DECLARE @line int

    DECLARE @vline varchar(4)

    DECLARE @objectid varchar(10)

    DECLARE @DECLARE varchar(8000)

    DECLARE @SELECT varchar(8000)

    DECLARE @EXECUTE varchar(8000)

    DECLARE @PRINT varchar(8000)

    DECLARE @SQL nvarchar(4000)

    SET @SQL = 'USE ' + @sourcedb + '

    SELECT @objectid = id, @lines = count(*)

    FROM ' + @sourcedb + '.dbo.syscomments

    WHERE id = object_id(@procedure)

    GROUP BY id'

    EXEC sp_executesql @SQL, N'@objectid int OUTPUT, @lines int OUTPUT, @procedure sysname', @objectid OUTPUT, @lines OUTPUT, @procedure

    SELECT @objectid , @lines , @procedure, @SQL

    SET @DECLARE = ''

    SET @SELECT = ''

    SET @EXECUTE = ''

    SET @PRINT = ''

    SET @line = 0

    WHILE @line <= @lines

    BEGIN

    SET @vline = @line

    SET @DECLARE = @DECLARE + char(10) + 'DECLARE @line' + @vline + ' nvarchar(4000)'

    SET @SELECT = @SELECT + char(10) + 'SELECT @line' + @vline + ' = text FROM ' + @sourcedb + '.dbo.syscomments WHERE colid = ' + @vline + ' AND id = ' + @objectid

    SET @EXECUTE = @EXECUTE + '+ @line' + @vline

    SET @PRINT = @PRINT + char(10) + 'PRINT @line' + @vline

    SET @line = @line + 1

    END

    SET @DECLARE = @DECLARE + CHAR(10)

    SET @SELECT = @SELECT + CHAR(10)

    SET @EXECUTE = 'USE ' + @targetDb + char(10) + 'EXECUTE (''''' + @EXECUTE + ')'

    SET @SQL = 'IF EXISTS (SELECT 1 FROM ' + @targetDB + '.dbo.sysobjects WHERE name = @procedure )

    DROP PROCEDURE ' + @procedure

    exec sp_executesql @SQL, N'@procedure sysname ', @procedure

    PRINT @DECLARE

    PRINT @SELECT

    PRINT @EXECUTE

    PRINT @PRINT

    EXECUTE (@DECLARE + @SELECT + @EXECUTE + @PRINT)

    GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply