Query SQL Server stored procedure text with JDBC

I needed to quickly print the text associated with several stored procedures across multiple database servers to individual files. I used what is below.

import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;
import java.io.*;

public class sqlProcedures {
  public static void main(String[] args) {
    String[] hosts = {"host1","host2","host3"};
    for (int i = 0; i < hosts.length; i++) {
      String connString = "jdbc:sqlserver://" + hosts[i] + ":1433;";
      try {
        Connection con = DriverManager.getConnection(connString + "databaseName=dbaDB;integratedSecurity=true;");
        PreparedStatement stm = con.prepareCall("select name,id from sysobjects where name like 'dbasp%' order by 1");
        ResultSet rst = stm.executeQuery();
 
        PreparedStatement stm2 = con.prepareCall("select text from syscomments where id = ?");
        while (rst.next()) {
          BufferedWriter out = new BufferedWriter(new FileWriter(hosts[i] + "_" + rst.getString(1) + ".sql"));
          stm2.setInt(1,rst.getInt(2));          
          ResultSet rst2 = stm2.executeQuery();
          while (rst2.next()) {
            out.write(rst2.getString(1));
          }
          out.close();
        }
      }
      catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
}

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.