Transferring stored procedures between databases

Kind as Microsoft are to give away SQL Server Express, one downside of the cut down version is it's lack of full data transfer services. As previously mentioned DTSWizard.exe can move tables between databases and servers, but it doesn't support moving stored procedures.

Running the following script in a query window with output set to text will produce all you SPs code. You can then copy & paste the text into your other database and run the script (assuming you've put the tables there first).


declare @objName sysname

declare objCurSPs cursor for  select [name] from sysobjects where xtype in ('P','TR')

OPEN objCurSPs
while 1=1
    begin
      — Fetch data
      fetch objCurSPs into @objName
     if @@fetch_status <> 0
        break;

    exec  sp_helptext @objName

END

CLOSE objCurSPs




Posted

in

by

Tags: