How do I find all database stored procedures in a database?

Created Nov 21, 1999

Lennart Jorelid

Use the getProcedures method of interface java.sql.DatabaseMetaData to probe the database for stored procedures. The exact usage is described in the code below.

public static void main(String[] args) throws Exception
{
      // Load the database driver - in this case, we
      // use the Jdbc/Odbc bridge driver.
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

      // Open a connection to the database
      Connection conn = DriverManager.getConnection("[jdbcURL]", 
                        "[login]", "[passwd]");

      // Get DatabaseMetaData
      DatabaseMetaData dbmd = conn.getMetaData();

      // Get all procedures.
      System.out.println("Procedures are called '" 
                    + dbmd.getProcedureTerm() +"' in the DBMS.");
      ResultSet rs = dbmd.getProcedures(null, null, "%");

      // Printout table data
      while(rs.next())
      {
          // Get procedure metadata
          String dbProcedureCatalog = rs.getString(1);
          String dbProcedureSchema = rs.getString(2);
          String dbProcedureName = rs.getString(3);
          String dbProcedureRemarks = rs.getString(7);
          short  dbProcedureType = rs.getShort(8);

          // Make result readable for humans
          String procReturn = (dbProcedureType == DatabaseMetaData.procedureNoResult 
                    ? "No Result" : "Result");

          // Printout
          System.out.println("Procedure: " + dbProcedureName 
                    + ", returns: " + procReturn);
          System.out.println("   [Catalog | Schema]: [" + dbProcedureCatalog 
                    + " | " + dbProcedureSchema + "]");
          System.out.println("   Comments: " + dbProcedureRemarks);
      }

      // Close database resources
      rs.close();
      conn.close();
}