Listing All Stored Procedures

JDBC Tutorials - Herong's Tutorial Examples - Version 3.03, by Dr. Herong Yang

JDBC Tutorials - Herong's Tutorial Examples

∟Microsoft JDBC Driver - DatabaseMetaData Object

∟Listing All Stored Procedures - getProcedures()

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 procedures in the AdventureWorks2014 database

/* ListProcedures.java
 - Copyright (c) 2015, HerongYang.com, All Rights Reserved.
 */
import java.sql.*;
public class ListProcedures {
  public static void main(String [] args) {
    Connection con = null;
    try {

      con = DriverManager.getConnection(
          "jdbc:sqlserver://localhost\\SQLEXPRESS;"
        + "user=sa;password=HerongY@ng;"
        + "database=AdventureWorks2014");

      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 (Exception e) {
      e.printStackTrace();
    }
  }
}

If you run this program, you will get:

C:\herong>\Progra~1\java\jdk1.8.0_45\bin\java 
   -cp .;\local\lib\sqljdbc41.jar ListProcedures

List of procedures: 
   ...
   AdventureWorks2014, sys, sp_MStestbit;1
   AdventureWorks2014, sys, sp_MStran_ddlrepl;1
   AdventureWorks2014, sys, sp_MStran_is_snapshot_required;1
   AdventureWorks2014, sys, sp_MStrypurgingoldsnapshotdeliveryprogress;1
   AdventureWorks2014, sys, sp_MSuniquename;1
   AdventureWorks2014, sys, sp_MSunmarkifneeded;1
   AdventureWorks2014, sys, sp_MSunmarkreplinfo;1
   AdventureWorks2014, sys, sp_MSunmarkschemaobject;1
   AdventureWorks2014, sys, sp_MSunregistersubscription;1
   AdventureWorks2014, sys, sp_MSupdate_agenttype_default;1
   AdventureWorks2014, sys, sp_MSupdate_singlelogicalrecordmetadata;1
   AdventureWorks2014, sys, sp_MSupdate_subscriber_info;1
   ...

Last update: 2015.

Table of Contents

About This Book

JDBC (Java Database Connectivity) Introduction

JDK (Java SE) Installation

Installing and Running Java DB - Derby

Derby (Java DB) JDBC Driver

Derby (Java DB) JDBC DataSource Objects

Java DB (Derby) - DML Statements

Java DB (Derby) - ResultSet Objects of Queries

Java DB (Derby) - PreparedStatement

MySQL Installation on Windows