JDBC Statement – DatabaseMetaData GetProcedures Example

Using the DatabaseMetaData interface you can obtain meta data all the procedures in the entire database or as is my case for a particular (Catalog and Schema). In this tutorial, we will specifically use getProcedures() to get details about stored procedures contained within the database. We will be using the getTables() method, passing in the following four parameters (“tutorial”, “dbo”, “%”, null). If you would prefer to get a list of all tables/views in the entire database then you can pass in null for all four parameters.

Common Methods used with DatabaseMetaData

Using the DatabaseMetaData instance, getProcedures() Method

DatabaseMetaData metadata = connection.getMetaData();
System.out.println("nList of Stored Procedures Available for 'dbo'...n");
ResultSet rs = metadata.getProcedures("tutorial", "dbo", null);
while (rs.next()) {
  System.out.format("%-15s %-10s %-40sn", rs.getString(1), rs.getString(2), rs.getString(3));
}

Creating Stored Proc DDL for SQL Server

CREATE PROCEDURE dbo.get_all_employees
as
BEGIN
  SELECT employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date
  FROM dbo.employee
  WHERE end_date is null
END

Full Program Listing

package com.avaldes.tutorials;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCDatabaseMetaDataProceduresExample {

  public static void main(String[] args)  throws SQLException {
    String database_url   = "jdbc:sqlserver://localhost:1433;databaseName=tutorial";
    String username     = "webuser";
    String password     = "webuser123";
    Connection connection   = null;
    
    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException e) {
      System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
      e.printStackTrace();
      return;
    }
   
    try {
      connection = DriverManager.getConnection(database_url, username, password);
    } catch (SQLException e) {
      System.out.println("ERROR:  Unable to establish a connection with the database!");
      e.printStackTrace();
      return;
    }
   
    try {
      if (connection != null) {
        DatabaseMetaData metadata = connection.getMetaData();
          System.out.println("nList of Stored Procedures Available for 'dbo'...n");
          System.out.format("%-15s %-10s %-40sn", "DATABASE", "SCHEMA", "PROCEDURE NAME");
          System.out.format("%-15s %-10s %-40sn", "-------------", "--------", "--------------------------------------------");
          ResultSet rs = metadata.getProcedures("tutorial", "dbo", null);
          //ResultSet rs = metadata.getTables(null, null, null);
          while (rs.next()) {
            System.out.format("%-15s %-10s %-40sn", rs.getString(1), rs.getString(2), 
                      rs.getString(3));
          }
          
      } else {
        System.out.println("ERROR: Unable to make a database connection!");
      }
      
      
    } catch (SQLException e) {
      e.printStackTrace();
      return;
    } finally {
        if (connection != null) connection.close();
    }
  }
}

Output

List of Stored Procedures Available for 'dbo'...

DATABASE        SCHEMA     PROCEDURE NAME                          
-------------   --------   --------------------------------------------
tutorial        dbo        get_all_employees;1   

Please Share Us on Social Media