Listing All Stored Procedures

JDBC for SQL Server - 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) 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=herong;password=T0pSecret;"
        + "database=AdventureWorks2019");

      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:

herong> java -cp .;mssql-jdbc-9.4.1.jre16.jar ListProcedures.java

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