Listing All Schemas - getSchemas()

<< Microsoft JDBC Driver - DatabaseMetaData Object

<< JDBC Tutorials - Herong's Tutorial Notes

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) 2007 by Dr. Herong Yang. All rights reserved.
 */
import java.sql.*;
public class ListSchemas {
  public static void main(String [] args) {
    Connection con = null;
    try {

      Class.forName(
        "com.microsoft.sqlserver.jdbc.SQLServerDriver");
      con = DriverManager.getConnection(
          "jdbc:sqlserver://localhost:1269;"
        + "user=sa;password=HerongYang;"
        + "database=AdventureWorksLT");

      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 (java.lang.ClassNotFoundException e) {
      System.err.println("ClassNotFoundException: "
        +e.getMessage());
    } catch (SQLException e) {
      System.err.println("SQLException: "
        +e.getMessage());
    }
  }
}

If you run this program, you will get:

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, dbo
   guest, guest
   public, null

But why we are getting "null" as the catalog names, which suppose to be the database name: AdventureWorksLT. Something wrong with Microsoft JDBC Driver 1.0.

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

use AdventureWorksLT;
select * from sys.schemas;
GO
name                 
---------------------
db_accessadmin       
db_backupoperator    
db_datareader        
db_datawriter        
db_ddladmin          
db_denydatareader    
db_denydatawriter    
db_owner             
db_securityadmin     
dbo                  
guest                
INFORMATION_SCHEMA   
SalesLT              
sys                  

Surprisingly, the list from sys.schemas gives two more schema names: SalesLT and INFORMATION_SCHEMA. This makes me to think that the JDBC schema concept is not mapped to the SQL Server schema concept.

As a final note, Microsoft 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)

Sections in This Chapter

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