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

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

SELECT  obj.id ,
        obj.name ,
        com.text
FROM    sysobjects obj
        JOIN syscomments com ON obj.id = com.id
WHERE   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();
			}
		}
	}

}