按每个文件导出数据库的所有存储过程

查询数据库的所有存储过程：

SELECT  obj.id ,        obj.name ,        com.textFROM    sysobjects obj        JOIN syscomments com ON obj.id = com.idWHERE   obj.xtype = 'P'ORDER BY com.id

按文件导出数据库的所有存储过程，具体代码如下：

package com.edenred;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.PrintWriter;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;public class GenerateProcedureFile {// 加载驱动private final String DBDRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";private final String DBURL = "jdbc:sqlserver://localhost:1433;databasename=myolay";private final String DBUSER = "sa";private final String DBPASSWORD = "abc123$";private Connection conn = null;public GenerateProcedureFile() {try {Class.forName(DBDRIVER);this.conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);} catch (Exception e) {e.printStackTrace(); }}public Connection getConnection() {return this.conn;}public void close() {try {this.conn.close();} catch (Exception e) {}}public static void main(String[] args) throws Exception {GenerateProcedureFile jdbcUtil = new GenerateProcedureFile();Connection conn = jdbcUtil.getConnection();PreparedStatement ps = conn.prepareStatement("SELECT id,name FROM sysobjects WHERE xtype ='P'");ResultSet rs = ps.executeQuery();while (rs.next()) {Integer id = rs.getInt("id");String name = rs.getString("name");StringBuffer sb = new StringBuffer();sb.append(" IF EXISTS ( SELECT * FROM sys.objects ").append("\r\n");sb.append(" WHERE   NAME = '").append(name).append("' )").append("\r\n");sb.append(" BEGIN ").append("\r\n");sb.append(" DROP PROCEDURE ").append(name).append("\r\n");sb.append(" END ").append("\r\n");sb.append(" GO ").append("\r\n");PreparedStatement pstemp = conn.prepareStatement("SELECT * FROM syscomments WHERE id = "+id);ResultSet rstemp = pstemp.executeQuery();while (rstemp.next()) {String text = rstemp.getString("text");sb.append(text);}generateFile(name, sb.toString());}//关闭数据库jdbcUtil.close();}// 生成文件public static void generateFile(String fileName, String fileContent) {FileOutputStream fos = null;PrintWriter pw = null;try {File file = new File("E:\\pro\\" + fileName.trim() + ".sql");// 创建文件file.createNewFile();fos = new FileOutputStream(file);// 写入数据fos.write(fileContent.getBytes());pw = new PrintWriter(fos);pw.write(fileContent.toCharArray());pw.flush();} catch (Exception e) {e.printStackTrace();} finally {if (fos != null) {try {fos.close();} catch (IOException e) {e.printStackTrace();}}if (fos != null) {pw.close();}}}}