DEVFYI - Developer Resource - FYI

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

JDBC Interview Questions and Answers


(Continued from previous question...)

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

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

(Continued on next question...)

Other Interview Questions