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();
}
}
}
}