This section describes how to get a list of all stored procedures in the current database on the SQL Server through the DatabaseMetaData object.
If you want to list all stored procedures in the current database,
you can use the DatabaseMetaData method: getProcedures(). Input parameters are:
catalog - a catalog name; must match the catalog name as it is stored in the database;
"" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
schemaPattern - a schema name pattern; must match the schema name as it is stored in the database;
"" retrieves those without a schema; null means that the schema name should not be used to narrow the search
procedureNamePattern - a procedure name pattern; must match the procedure name as it is stored in the database
The output is captured in a ResultSet object with
the following fields:
PROCEDURE_CAT String => table catalog (may be null)
PROCEDURE_SCHEM String => table schema (may be null)
PROCEDURE_NAME String => procedure name
The following sample program displays all columns in the fyi_links table:
/**
* ListProcedures.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class ListProcedures {
public static void main(String [] args) {
Connection con = null;
try {
Class.forName(
"com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(
"jdbc:sqlserver://localhost:1269;"
+ "user=sa;password=HerongYang;"
+ "database=AdventureWorksLT");
DatabaseMetaData meta = con.getMetaData();
ResultSet res = meta.getProcedures(null, null, null);
System.out.println("List of procedures: ");
while (res.next()) {
System.out.println(
" "+res.getString("PROCEDURE_CAT")
+ ", "+res.getString("PROCEDURE_SCHEM")
+ ", "+res.getString("PROCEDURE_NAME"));
}
res.close();
con.close();
} catch (java.lang.ClassNotFoundException e) {
System.err.println("ClassNotFoundException: "
+e.getMessage());
} catch (SQLException e) {
System.err.println("SQLException: "
+e.getMessage());
}
}
}
