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

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();
}
0 Comments  (click to add your comment)
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

About | Sitemap | Contact