How to copy stored procedures

[crazyrush]

Hi All,

I want to copy all stored procedures from one DB server to another(on different machine) .
I know how to do tables and using query builder as well.
Is there any generic method to import/export all stored procedures?

thanks

[womalley]

Try this

DECLARE @objDMO int
DECLARE @objDatabase int
DECLARE @dbname varchar(200)
DECLARE @procedurename varchar(200)
DECLARE @cmd varchar(300)
DECLARE @temp varchar(8000)

SET @dbname = 'Your Database'
SET @procedurename = 'dbo.PROCEDURE_NAME'

EXEC sp_OACreate 'SQLDMO.SQLServer', @objDMO OUT
EXEC sp_OASetProperty @objDMO, 'loginsecure', 'true'
EXEC sp_OAMethod @objDMO, 'Connect', NULL, '(local)'

SET @cmd = 'Databases("' + @dbname + '").Procedur("' + @procedurename + '").Script'
EXEC sp_OAMethod @objDMO, @cmd , @temp OUTPUT, 4
PRINT @temp

EXEC sp_OADestroy @objDMO

SELECT * FROM SYSOBJECTS

declare @cmd2 varchar(255)
declare GetTriggers cursor fast_forward
for
select 'sp_helptext [' + name + ']' from sysobjects where type = 'P'
open GetTriggers
fetch next from GetTriggers into @cmd2

while @@fetch_status = 0
begin
exec (@cmd2)
print 'GO'
fetch next from GetTriggers into @cmd2
end

Will

[crazyrush]

Thank you very much for your reply.
But I'm still not sure how will I execute this method.

Let's say my config is as follows then where & what parameters need to be passed...

Source Machine Name - M1
Source DB Name - DB1

Destination Machine Name - M2
Destination DB Name - DB2

waiting for reply...
thanks.