CODEWITHSUNDEEP
javamysqljdbc
Kaushal Daga
Kaushal Daga

Reputation: 83

How to get primary keys for all tables in JDBC?

I have a database with at least 500 tables. What is the exact code to get all the primary keys and foreign keys of each table?

//Primary Key
DatabaseMetaData meta=conn.getMetaData();
ResultSet rs1= meta.getTables(null, null, "TableName" , new String[]{"TABLE"});
rs1=meta.getPrimaryKeys(null, null, "TableName");
while(rs1.next())
    System.out.println("Primary Key :"+rs1.getString(4));

//Foreign Key
rs1=meta.getExportedKeys(null, null, "TableName");
while(rs1.next())
    System.out.println("Foreign Key :"+rs1.getString(4));

I have used this code and it gives me accurate keys but for 500 tables, I have to change my code 500 times. Is there any way to minimize this effort?

Upvotes: 8

Views: 14863

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 108982

You don't need to modify your code 500 times, you can retrieve all table names using meta.getTables(null, null, "%", new String[]{"TABLE"}).

The method getTables takes a like-pattern for the tableNamePattern parameter, so "%" matches all table names.

The method getPrimaryKeys and getExportedKeys do not take a pattern, so you will need to loop over the result of getTables and execute those methods for each row of the getTables result set.

So you will need to do something like:

DatabaseMetaData meta = conn.getMetaData();
try (ResultSet tables = meta.getTables(null, null, "%", new String[] { "TABLE" })) {
    while (tables.next()) {
        String catalog = tables.getString("TABLE_CAT");
        String schema = tables.getString("TABLE_SCHEM");
        String tableName = tables.getString("TABLE_NAME");
        System.out.println("Table: " + tableName);
        try (ResultSet primaryKeys = meta.getPrimaryKeys(catalog, schema, tableName)) {
            while (primaryKeys.next()) {
                System.out.println("Primary key: " + primaryKeys.getString("COLUMN_NAME"));
            }
        }
        // similar for exportedKeys
    }
}

I have included retrieval of catalog and schema, because that might influence how things work.

Upvotes: 9

N00b Pr0grammer
N00b Pr0grammer

Reputation: 4647

You can refer to this code snippet that shows you how to get all the tables from a database.

You need to iterate over your ResultSet calling next().

I have tried this for MS SQL Server 2012

public class RetrieveAllTables {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=HubDB", "sa", "password");
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            ResultSet resultSet = databaseMetaData.getTables(null, null, "%", new String[] {"TABLE"});

            while(resultSet.next()) {
                System.out.print("Table Catalog: " + resultSet.getString("TABLE_CAT"));
                System.out.print("\tTable Schema: " + resultSet.getString("TABLE_SCHEM"));
                System.out.print("\tTable Name: " + resultSet.getString("TABLE_NAME"));
                System.out.println();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Returns the following as output:

Table Catalog: HubDB    Table Schema: dbo   Table Name: films
Table Catalog: HubDB    Table Schema: dbo   Table Name: FILMS_AUDIT
Table Catalog: HubDB    Table Schema: sys   Table Name: trace_xe_action_map
Table Catalog: HubDB    Table Schema: sys   Table Name: trace_xe_event_map

Hence you need to change the following from

ResultSet resultSet = databaseMetaData.getTables(null, null, "%", new String[] {"TABLE"});

to

ResultSet resultSet = databaseMetaData.getTables(null, "dbo", "%", new String[] {"TABLE"});

You can refer the getTables() method documentation here

Using this information, you can dynamically pass the individual TableName to retrieve the Primary Key and the Foreign Key details. Your existing code should work just fine in retrieving these details by passing just one parameter, that's the TableName from the above code.

Upvotes: 4

Related Questions