June 6, 2005 at 8:05 am
Hi,
I want to copy all of the Stored Procedures in one dbase to another. I have used the import/export but it fails each time I try. Is there any other way of doing it? (I have tried importing into dbase1 from dbase2 - and exporting from dbase2 to dbase1)
Can it be done from query analyzer?
thanks,
Paul
June 6, 2005 at 8:19 am
Use Enterprise Manager to script all of the stored procedures from the database.
Then you can execute the created script when connected to the new database in Query Analyzer.
Rick
June 7, 2005 at 5:02 am
Yes, you can also generate the script in query analyzer. Right-click the sproc and select "script to new window as create", or to file if you want. In query analzyer you can only script one object at a time. In EM you are able to select as many objects as you like.
The only other technique is to use a 3rd party database comparision tool.
June 7, 2005 at 6:49 am
You can also use the sp_helptext stored procedure to get the text of a stored procedure (in Query Analyzer or osql):
sp_helptext sp_xxxxx
Regards,
Carl
June 7, 2005 at 7:05 am
Hi,
you could use something like:
/* Procedure to script all user-procedures, -functions and views on a server
Author: Karl Klingler
Created 20.10.2004
Modified the code of:
** Autor Rodrigo Acosta
** Export the code of all sp into a text file
** Fecha 08/04/2003
*/
CREATE PROCEDURE sp_script_user_objects AS
BEGIN
SET NOCOUNT ON
/* If it exists, deletes the tmp table */
IF (OBJECT_ID('tempdb.dbo.##SPs')) IS NOT NULL
DROP TABLE ##SPs
--GO
/* creates the table wich saves the sp text */
CREATE TABLE ##SPs
([text] text)
--GO
INSERT INTO ##SPs
([text])
VALUES
('-- Server Name:' + @@SERVERNAME)
-- Cursor for DB-names
DECLARE dbnames_cursor CURSOR
FOR
SELECT name
FROM dbo.sysdatabases where name 'tempdb'
OPEN dbnames_cursor
DECLARE @dbname sysname
-- get all DB-names and do the commands for each db
FETCH NEXT FROM dbnames_cursor INTO @dbname
WHILE (@@FETCH_STATUS -1)
BEGIN
IF (@@FETCH_STATUS -2)
BEGIN
SELECT @dbname = RTRIM(@dbname)
PRINT '/* Scripts for user-objects in Dateabase '+@dbname+' */'
INSERT INTO ##SPs ([text]) VALUES ('-- Database Name: ' + DB_NAME())
INSERT INTO ##SPs ([text]) VALUES ('-- Date:' + CONVERT(VARCHAR, GETDATE()))
INSERT INTO ##SPs ([text]) VALUES ('-- Stored prodecure generated automatically by user ' + USER_NAME())
INSERT INTO ##SPs ([text]) VALUES ('GO')
DECLARE @sp-2 AS SYSNAME
DECLARE @STR AS VARCHAR(500)
/* cursor wich will save all the sp in the database */
DECLARE curSP CURSOR LOCAL FOR
SELECT name
FROM sysobjects
WHERE (xtype = 'P' OR xtype = 'V' OR xtype = 'FN')
AND (category = 0)
ORDER BY name
OPEN curSP
FETCH NEXT FROM curSP INTO @sp-2
WHILE (@@FETCH_STATUS = 0)
BEGIN
/* insert in the temp table, the text of the sp */
SELECT @STR = 'EXEC sp_helptext ' + @sp-2
INSERT INTO ##SPs EXEC (@str)
INSERT INTO ##SPs ([text]) VALUES ('GO')
PRINT 'Stored Procedure ' + @sp-2 + ' Inserted.'
FETCH NEXT FROM curSP INTO @sp-2
END
CLOSE curSP
DEALLOCATE curSP
END
PRINT convert(char(25),getdate(),113)+': Scripts for Dateabase '+@dbname+' generated.'
FETCH NEXT FROM dbnames_cursor INTO @dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
INSERT INTO ##SPs ([text]) VALUES ('-- END of Generation-Run ')
/* Now I generate the file */
DECLARE @bcp AS VARCHAR(8000)
DECLARE @status AS INT
PRINT ''
PRINT 'Generating .txt...'
SELECT @bcp = 'bcp "SELECT * FROM ##SPs" QUERYOUT "T:\Backups\StoredProcs_' + @@SERVERNAME + '.txt" -T -c -q'
EXEC @status = master.dbo.xp_cmdshell @bcp, no_output
IF @status 0
BEGIN
PRINT 'An error ocurred while generaring the txt file.'
RETURN
END ELSE
PRINT 'C:\StoredProcs_' + @@SERVERNAME + '.txt file generated succesfully.'
--declare @cmd sysname
--select @cmd = 'SELECT * FROM ##SPs'
--exec (@cmd)
/* If it exists, deletes the tmp table */
IF (OBJECT_ID('tempdb.dbo.##SPs')) IS NOT NULL
DROP TABLE ##SPs
PRINT convert(char(25),getdate(),113)+'/* Scripts for user-objects created. */'
WAITFOR DELAY '00:00:05'
END
GO
Best regards
karl
June 7, 2005 at 7:51 am
Be aware that if you script your stored procedures using EM, they are generated in alphabetical order (case sensative, upper case first, then lower case).
If you have any nested stored procedures (say, proc A calls proc B), then you may get errors trying to recreate the stored procedures at the new location. You will have to manually reorganize the file so that dependencies are account for. (In the example above, move proc B above proc A in the file so that B is created first).
June 7, 2005 at 9:40 am
A commercial solution would be to use the red-gate package advertised extensivly on this site.. we use this for many applications such as moving dev db's to production.
Works a treat..
It'll even tell you what the differences are between the two db's
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply