12

How can i copy/export all stored procedures on MS SQL Server from my machine to a machine of my project partner ? It might be a basic question. Apologies.

Thank you.

try
  • 175
  • 1
  • 2
  • 7
  • 2
    [Generate a separate script file for each stored procedure in a SQL Server database](https://stackoverflow.com/a/35722051/2794280) – kgzdev Oct 24 '18 at 14:23
  • Tell him to get them from the source code repository. Don't store the source code in the database. – The Impaler Oct 24 '18 at 14:50

3 Answers3

16

It is s simple thing in SQL Management Studio, right click on the database go to tasks then to generate scripts and select stored proceduresenter image description here

China Syndrome
  • 953
  • 12
  • 24
4

You can extract the definitions of all stored procedures with this simple script.

select m.definition
from
    sys.objects as o
    inner join
        sys.sql_modules as m
            on o.object_id = m.object_id
where
    o.type = 'p'

Afterwards, you could run them all on the other machine. Yet, it barely ever is that easy. There might be underlying schemas or types which need to be there first. How about access to other databases or even linked servers within the stored procedure? All of these dependencies need to be addressed and resolved first before you can transfer the stored procedure to the other server.

Ralph
  • 9,284
  • 4
  • 32
  • 42
  • 1
    Though possible, I strongly disagree about sharing them from one database to another. Use the source code repository. Otherwise the source will be out of control. – The Impaler Oct 24 '18 at 14:51
  • 2
    @TheImpaler I guess that implies that you actually have a source code repository and that you are running some kind of software to connect to that source code repository and keep it in sync with your actual commits. Furthermore, you'd need afterwards on the "project manager" the same setup to apply the commits to the secondary server / database. I understand that this can be easily setup and implemented (if their corporate environment allows such software, add-ins, and access to a possibly off-premises hosted repository). But this is going (in my opinion) way beyond the question asked. – Ralph Oct 25 '18 at 06:45
  • 2
    I unfortunately agree with all you say. – The Impaler Oct 25 '18 at 14:12
2

You can generate script as follows-

  • Right click on database
  • Tasks
  • Generate Scripts
  • Select the objects you wish to script
  • Script to File
  • Run generated scripts against target database

If you want to select more than one objects (like SPs) tick the top-level checkbox beside "Stored Procedures" it selects them all together on 1 click.

Dev
  • 357
  • 2
  • 11