November 11, 2002 at 9:46 am
Does someone knows some script that allows me to copy a stored from a DB to another DB, especially if the stored definition is larger than 4000 chars?
November 11, 2002 at 9:28 pm
Are you referring to a stored procedure?
November 12, 2002 at 1:32 am
Yes
November 12, 2002 at 1:37 am
you could generate a script with enterprise manager na drun that script in the other db
November 12, 2002 at 1:45 am
My problem is that I have a DB in which I develop an application and then many other DBs where I have to update the structural changes. So when I modify a Stored then I have to generate the script to ALTER the stored and then make it run manually on the other DBs. I would like to have a command like "copy_stored <name> <sourceDB> <destDB>" so I can cycle my DBs and exec the command programmatically. I found that querying the INFORMATION_SCHEMA.ROUTINES I can get a stored definition, but it's limited to the first 4000 chars
November 12, 2002 at 4:02 am
Then you really have two choices
1) Use Export wizard to export the SPs to the remote servers.
2) Use the generate SQL script option to generate a single script file with drop and create of the SP's or do creates then open the file to edit and do a replace on CREATE PROCEDURE to ALTER PROCEDURE and do replace all. And then run the final product against all servers.
November 12, 2002 at 4:52 am
Try this have posted as a sciprt as well so should be available soon.
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
/*******************************************************************************
Written By : Simon Sabin
Date : 12 November 2002
Description : Copies a stored procedure from one database to another
:
History
Date Change
------------------------------------------------------------------------------
12/11/2002 Created
*******************************************************************************/
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
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply