Listing All Schemas - getSchemas()

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 Schemas - getSchemas()

This section describes how to get a list of all schemas in the current database on the SQL Server through the DatabaseMetaData object.

If you want list all schemas in the current database on the SQL server, you can use the DatabaseMetaData method: getSchemas(). It returns all schemas in the current database. The output is captured in a ResultSet object with two field, TABLE_SCHEM and TABLE_CATALOG.

The following sample program displays all schemas in the AdventureWorksLT database:

/* ListSchemas.java
 - Copyright (c) 2015, HerongYang.com, All Rights Reserved.
 */
import java.sql.*;
public class ListSchemas {
  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.getSchemas();
      System.out.println("List of schemas: "); 
      while (res.next()) {
         System.out.println(
            "   "+res.getString("TABLE_SCHEM") 
           + ", "+res.getString("TABLE_CATALOG")); 
      }
      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 ListSchemas

List of schemas:
   db_accessadmin, null
   db_backupoperator, null
   db_datareader, null
   db_datawriter, null
   db_ddladmin, null
   db_denydatareader, null
   db_denydatawriter, null
   db_owner, null
   db_securityadmin, null
   dbo, null
   guest, null
   INFORMATION_SCHEMA, null
   sys, null
   HumanResources, AdventureWorks2014
   Person, AdventureWorks2014
   Production, AdventureWorks2014
   Purchasing, AdventureWorks2014
   Sales, AdventureWorks2014

To see if the list is correct or not, I checked the system view sys.schemas directly:

use AdventureWorks2014;
select * from sys.schemas order by name;
GO
name                 
---------------------
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin
dbo
guest
HumanResources
INFORMATION_SCHEMA
Person
Production
Purchasing
Sales
sys

Both lists match pretty well. But if you are using JDBC Driver 1.0, the list from sys.schemas gives more schema names.

Also note that JDBC Driver 1.0 does not support getSchemas(String catalog, String schemaPattern), which was introduced in Java SE 6. If you try it, you will get this error:

Exception in thread "main" java.lang.AbstractMethodError: 
com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.
getSchemas(Ljava/lang/String;Ljava/lang/String;)
Ljava/sql/ResultSet;
        at ListSchemas.main(ListSchemas.java:16)

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