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

      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:

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

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, AdventureWorks2019
   Person, AdventureWorks2019
   Production, AdventureWorks2019
   Purchasing, AdventureWorks2019
   Sales, AdventureWorks2019

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)

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

 MySQL JDBC Driver (MySQL Connector/J)

 MySQL - PreparedStatement

 MySQL - Reference Implementation of JdbcRowSet

 MySQL - JBDC CallableStatement

 MySQL CLOB (Character Large Object) - TEXT

 MySQL BLOB (Binary Large Object) - BLOB

 Oracle Express Edition Installation on Windows

 Oracle JDBC Drivers

 Oracle - Reference Implementation of JdbcRowSet

 Oracle - PreparedStatement

 Oracle - JBDC CallableStatement

 Oracle CLOB (Character Large Object) - TEXT

 Oracle BLOB (Binary Large Object) - BLOB

 Microsoft SQL Server Express Edition

 Microsoft JDBC Driver for SQL Server

 Microsoft JDBC Driver - Query Statements and Result Sets

Microsoft JDBC Driver - DatabaseMetaData Object

 Commonly Used DatabaseMetaData Methods

 Getting Database Server and Driver Info

 Listing All Databases - getCatalogs()

Listing All Schemas - getSchemas()

 Listing All Tables - getTables()

 Listing All Culumns - getColumns()

 Listing All Stored Procedures - getProcedures()

 Microsoft JDBC Driver - DDL Statements

 Microsoft JDBC Driver - DML Statements

 SQL Server - PreparedStatement

 SQL Server CLOB (Character Large Object) - TEXT

 SQL Server BLOB (Binary Large Object) - BLOB

 JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver

 JDBC-ODBC Bridge Driver - Flat Text Files

 JDBC-ODBC Bridge Driver - MS Access

 JDBC-ODBC Bridge Driver - MS SQL Server

 Summary of JDBC Drivers and Database Servers

 Using Connection Pool with JDBC

 Archived Tutorials

 References

 Full Version in PDF/EPUB