Export multiple stored procedures to text files

user_11078053 0
Viewed 3191

In SQL Server Management Studio, I have a database with 200 stored procedures. I'm exporting each stored procedure as a script, by right clicking -> script stored procedure -> CREATE To -> File.

Is it possible to export all procedures at once, using a powershell script or anything else ?

Thank you,

sql-server
3 Answers

Use the Generate Scripts tool in SSMS:

  1. Right Click Database in Object Explorer.
  2. Tasks -> Generate Scripts.
  3. If given the "tutorial" click Next.
  4. Select "Select specific database objects" and tick "Stored Procedures". Click Next.
  5. Choose export method. Likely here you want "Save as script file" with "one script file per object" selected. Ensure you choose the export location.
  6. Click Next and Finish buttons as required.
user_20299830

If you are using MGT for MSSQL, then you follow these steps.

  1. open MGT MSSQL and connect and open the list of databases
  2. right button the the database where the stored procs are, this will open the con menu
  3. go to "Tasks"
  4. select "Generate Scripts"
  5. select specific object, in your case, select "Stored Procedures" (you can select all or as you need)
  6. then press "Next"
  7. at the "Set Scripting Options" form, select the option; if you want all selected procs in one file or not, choose the directory where you want your files to be generated.
  8. press "Next"
  9. at the "Summary" form press "Next"
  10. at the "Save or Publish Scripts" Press finish..

DONE.

There are advance options at step 7. to create each procs with drop and create option or just create options. go through those if you need.

tc.

user_33790310

I have developed a C# application that can accomplish this, scripting out all tables, views, stored procedures, functions, etc. to text files (one per object). This is very useful for scripting out the objects on a regular basis then keeping track of them in a Git repository. Download the DB Schema Export Tool from https://github.com/PNNL-Comp-Mass-Spec/DB-Schema-Export-Tool/releases

Example command line for automation:

DB_Schema_Export_Tool.exe 
  C:\Cached_DBSchema 
  /Server:Proteinseqs 
  /DBList:Manager_Control,Protein_Sequences 
  /Sync:"F:\Projects\Database_Schema\DMS" 
  /Git /Commit 
  /L /LogDir:Logs 
  /Data:ProteinSeqs_Data_Tables.txt

On Linux, use mono DB_Schema_Export_Tool.exe

  • Information on Mono

There are other command line switches available; see the Readme. In addition to SQL Server, the software supports Postgres. Example output files can be found at https://github.com/PNNL-Comp-Mass-Spec/DBSchema_DMS/tree/master/DMS_Pipeline

user_11794670
Related