SQL Server 将多个存储过程导出到文本文件

svmlkihl  于 2022-12-10  发布在  其他
关注(0)|答案(3)|浏览(143)

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?

xqk2d5yq

xqk2d5yq1#

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.
6ljaweal

6ljaweal2#

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.
8qgya5xd

8qgya5xd3#

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

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

相关问题