Read stored procedure source code from Sybase ASE 15.7 database

Question

I want to get access to the source code of some stored procedures, which are stored in a Sybase ASE 15.7 database. At the moment I copy the text manually in a textfile and read it via executed java-code from the disk.

Now it is my goal to improve this process: I will get the sourcecode directly from the database within my executed java-code. Is that possible? Sorry, I have no idea.

I have Rapid SQL to work on the database.

Answer 1

Vince's hint was to use the stored procedure sp_helptext. This stored procedure produces more than one resultset and caused me difficulties.

RobV's answer was to use the stored procedure sp_showtext. This seems the better solution, cause it returns only one resultset. But I tried it with jdbc and it ever throws in a sql exception ("must return a resultset").

After my fails I find another solution, which need against the other two answers only a execution of a select statement. This works for me! Here is the code:

    StringBuffer sbSP = new StringBuffer();
    String query = "select c.text "
                  +"from syscomments c, sysobjects o "
                    +"where o.id=c.id and o.name='"+spName+"' order by c.colid";

    PreparedStatement stmt = connection.prepareStatement(query);
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
        //loop over every row of the stored procedure
        sbSP.append(rs.getString("text") + "\n");
    }