How do I use DatabaseMetaData to inspect schema in JDBC?

You use java.sql.DatabaseMetaData by:

1. Opening a Connection
2. Calling conn.getMetaData()
3. Querying catalogs/schemas/tables/columns/keys/indexes via the DatabaseMetaData “getXxx” methods (they mostly return ResultSets)
4. Reading those result sets like normal query results

Below are the most common inspection tasks and the JDBC calls that power them.

---

1) Get the DatabaseMetaData

package org.kodejava.jdbc;

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

public class DatabaseMetadata1 {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {

            DatabaseMetaData md = conn.getMetaData();

            System.out.println(md.getDatabaseProductName() + " " + md.getDatabaseProductVersion());
            System.out.println(md.getDriverName() + " " + md.getDriverVersion());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

If you’re using a DataSource, it’s the same idea: try (Connection conn = dataSource.getConnection()) { ... }.

---

2) Understand the “filter” parameters (the #1 gotcha)

Many methods take filters like:

• catalog (often the database name; sometimes unused)
• schemaPattern (SQL pattern: % wildcard; sometimes case-sensitive depending on DB)
• tableNamePattern (pattern)
• types (e.g., {"TABLE","VIEW"})

Patterns are not regex; they’re SQL LIKE-style patterns:

• % = any sequence
• _ = any single character

Also: some databases store identifiers in upper-case/lower-case internally. Use:

• md.storesUpperCaseIdentifiers()
• md.storesLowerCaseIdentifiers()
• md.supportsSchemasInTableDefinitions()

---

3) List catalogs and schemas

package org.kodejava.jdbc;

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

public class DatabaseMetadata2 {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
            DatabaseMetaData md = conn.getMetaData();

            try (ResultSet rs = md.getCatalogs()) {
                while (rs.next()) {
                    System.out.println("Catalog: " + rs.getString("TABLE_CAT"));
                }
            }

            try (ResultSet rs = md.getSchemas()) {
                while (rs.next()) {
                    System.out.println("Schema: " + rs.getString("TABLE_SCHEM")
                                       + " (catalog=" + rs.getString("TABLE_CATALOG") + ")");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Column names like "TABLE_SCHEM" are specified by the JDBC spec for each metadata result set. (They feel a bit “old-school”, but they’re standard.)

---

4) List tables (and views)

package org.kodejava.jdbc;

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

public class DatabaseMetadata3 {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
            DatabaseMetaData md = conn.getMetaData();

            String catalog = null;            // or your catalog name
            String schemaPattern = "public";  // adjust for your DB; or "%"
            String tablePattern = "%";
            String[] types = {"TABLE", "VIEW"};

            try (ResultSet rs = md.getTables(catalog, schemaPattern, tablePattern, types)) {
                while (rs.next()) {
                    String schema = rs.getString("TABLE_SCHEM");
                    String table  = rs.getString("TABLE_NAME");
                    String type   = rs.getString("TABLE_TYPE");
                    System.out.println(type + " " + schema + "." + table);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

---

5) Inspect columns for a table

package org.kodejava.jdbc;

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

public class DatabaseMetadata4 {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
            DatabaseMetaData md = conn.getMetaData();

            String catalog = null;
            String schema = "public";
            String table = "users";

            try (ResultSet rs = md.getColumns(catalog, schema, table, "%")) {
                while (rs.next()) {
                    String col = rs.getString("COLUMN_NAME");
                    int jdbcType = rs.getInt("DATA_TYPE");          // java.sql.Types
                    String dbType = rs.getString("TYPE_NAME");      // vendor type name
                    int size = rs.getInt("COLUMN_SIZE");
                    int nullable = rs.getInt("NULLABLE");           // DatabaseMetaData.columnNullable/NoNulls
                    String def = rs.getString("COLUMN_DEF");

                    System.out.printf("%s %s(%d) nullable=%s default=%s%n",
                            col, dbType, size,
                            (nullable == DatabaseMetaData.columnNullable),
                            def);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

If you need precision/scale for numeric columns, also read:

• DECIMAL_DIGITS
• NUM_PREC_RADIX

---

6) Primary keys, foreign keys, and indexes

Primary key

try (ResultSet rs = md.getPrimaryKeys(null, "public", "users")) {
    while (rs.next()) {
        System.out.println("PK column: " + rs.getString("COLUMN_NAME")
                + " (name=" + rs.getString("PK_NAME") + ")");
    }
}

Foreign keys (imported keys = FKs from this table to others)

try (ResultSet rs = md.getImportedKeys(null, "public", "users")) {
    while (rs.next()) {
        System.out.println("FK " + rs.getString("FK_NAME")
                + ": " + rs.getString("FKCOLUMN_NAME")
                + " -> " + rs.getString("PKTABLE_NAME") + "." + rs.getString("PKCOLUMN_NAME"));
    }
}

Indexes

package org.kodejava.jdbc;

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

public class DatabaseMetadata5 {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
            DatabaseMetaData md = conn.getMetaData();

            boolean unique = false; // set true to request only unique indexes
            boolean approximate = false;

            try (ResultSet rs = md.getIndexInfo(null, "public", "users", unique, approximate)) {
                while (rs.next()) {
                    String indexName = rs.getString("INDEX_NAME");
                    String column = rs.getString("COLUMN_NAME");
                    boolean nonUnique = rs.getBoolean("NON_UNIQUE");
                    short type = rs.getShort("TYPE"); // tableIndexStatistic / tableIndexClustered / etc.
                    if (indexName != null && column != null) {
                        System.out.println((nonUnique ? "IDX" : "UNIQUE") + " " + indexName + " on " + column + " type=" + type);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

---

7) “Dump the schema” pattern (tables → columns → keys)

For a basic schema inspection tool, you typically:

1. getTables(...)
2. For each table: getColumns(...)
3. Optionally: getPrimaryKeys(...), getImportedKeys(...), getIndexInfo(...)

Keep it read-only; it’s metadata only.

---

8) Practical tips / pitfalls

• Case sensitivity: If your DB stores identifiers uppercase, but you pass lowercase (or vice versa), you may get empty results. Check storesUpperCaseIdentifiers() / storesLowerCaseIdentifiers() and normalize.
• Use % liberally while exploring: Start with schemaPattern="%" and narrow down once you see what the DB reports.
• Some drivers are quirky: Not all metadata is perfectly implemented. When something looks missing, verify against the DB’s own system catalogs.
• Close metadata ResultSets: They’re real JDBC resources. Use try-with-resources as shown.