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