I am trying to list all the indexes for all the tables in my DB (using a generic solution which is not specific to an RDBMS)
To test out my solution, I have 2 tables in an MS SQL Server DB tempdb
- Stores
- Staffs
Each table has a PK and the staffs table also have a Unique key column called email.
There is a composite index on the stores table on store_id, store_name.
Following are the indexes (screenshots from DBeaver)
The code snippet is as follows
.
.
.
System.out.println("**********************************************************");
System.out.println("schema >>> "+schema);
System.out.println("table >>> "+table);
ResultSet indexValues = tableMeta.getIndexInfo(null, null, table, true, false);
while (indexValues.next()) {
ResultSetMetaData rsmd = indexValues.getMetaData();
String idxName = indexValues.getString("INDEX_NAME");
System.out.println("=====================================================");
System.out.println("rs1 >>> "+indexValues.getString(1));
System.out.println("rs2 >>> "+indexValues.getString(2));
System.out.println("rs3 >>> "+indexValues.getString(3));
System.out.println("rs4 >>> "+indexValues.getString(4));
System.out.println("rs5 >>> "+indexValues.getString(5));
System.out.println("rs6 >>> "+indexValues.getString(6));
System.out.println("rsmd >>> "+rsmd.getColumnCount());
System.out.println("INDEX_NAME >>> "+indexValues.getString("INDEX_NAME"));
System.out.println("INDEX_QUALIFIER >>> "+indexValues.getString("INDEX_QUALIFIER"));
System.out.println("NON_UNIQUE >>> "+indexValues.getBoolean("NON_UNIQUE"));
System.out.println("TYPE >>> "+indexValues.getShort("TYPE"));
System.out.println("ORDINAL_POSITION >>> "+indexValues.getString("ORDINAL_POSITION"));
System.out.println("COLUMN_NAME >>> "+indexValues.getString("COLUMN_NAME"));
System.out.println("ASC_OR_DESC >>> "+indexValues.getString("ASC_OR_DESC"));
System.out.println("FILTER_CONDITION >>> "+indexValues.getString("FILTER_CONDITION"));
System.out.println("TABLE_SCHEM >>> "+indexValues.getString("TABLE_SCHEM"));
System.out.println("TABLE_CAT >>> "+indexValues.getString("TABLE_CAT"));
System.out.println("=====================================================");
}
The output is as follows (the compound/composite index is not returned)
**********************************************************
schema >>> dbo
table >>> stores
=====================================================
rs1 >>> tempdb
rs2 >>> dbo
rs3 >>> stores
rs4 >>> null
rs5 >>> null
rs6 >>> null
rsmd >>> 13
INDEX_NAME >>> null
INDEX_QUALIFIER >>> null
NON_UNIQUE >>> false
TYPE >>> 0
ORDINAL_POSITION >>> null
COLUMN_NAME >>> null
ASC_OR_DESC >>> null
FILTER_CONDITION >>> null
TABLE_SCHEM >>> dbo
TABLE_CAT >>> tempdb
=====================================================
=====================================================
rs1 >>> tempdb
rs2 >>> dbo
rs3 >>> stores
rs4 >>> 0
rs5 >>> stores
rs6 >>> PK__stores__A2F2A30C0F20F60B
rsmd >>> 13
INDEX_NAME >>> PK__stores__A2F2A30C0F20F60B
INDEX_QUALIFIER >>> stores
NON_UNIQUE >>> false
TYPE >>> 1
ORDINAL_POSITION >>> 1
COLUMN_NAME >>> store_id
ASC_OR_DESC >>> A
FILTER_CONDITION >>> null
TABLE_SCHEM >>> dbo
TABLE_CAT >>> tempdb
=====================================================
**********************************************************
schema >>> dbo
table >>> staffs
=====================================================
rs1 >>> tempdb
rs2 >>> dbo
rs3 >>> staffs
rs4 >>> null
rs5 >>> null
rs6 >>> null
rsmd >>> 13
INDEX_NAME >>> null
INDEX_QUALIFIER >>> null
NON_UNIQUE >>> false
TYPE >>> 0
ORDINAL_POSITION >>> null
COLUMN_NAME >>> null
ASC_OR_DESC >>> null
FILTER_CONDITION >>> null
TABLE_SCHEM >>> dbo
TABLE_CAT >>> tempdb
=====================================================
=====================================================
rs1 >>> tempdb
rs2 >>> dbo
rs3 >>> staffs
rs4 >>> 0
rs5 >>> staffs
rs6 >>> PK__staffs__1963DD9CCB589A48
rsmd >>> 13
INDEX_NAME >>> PK__staffs__1963DD9CCB589A48
INDEX_QUALIFIER >>> staffs
NON_UNIQUE >>> false
TYPE >>> 1
ORDINAL_POSITION >>> 1
COLUMN_NAME >>> staff_id
ASC_OR_DESC >>> A
FILTER_CONDITION >>> null
TABLE_SCHEM >>> dbo
TABLE_CAT >>> tempdb
=====================================================
=====================================================
rs1 >>> tempdb
rs2 >>> dbo
rs3 >>> staffs
rs4 >>> 0
rs5 >>> staffs
rs6 >>> UQ__staffs__AB6E6164E6F8EDB6
rsmd >>> 13
INDEX_NAME >>> UQ__staffs__AB6E6164E6F8EDB6
INDEX_QUALIFIER >>> staffs
NON_UNIQUE >>> false
TYPE >>> 3
ORDINAL_POSITION >>> 1
COLUMN_NAME >>> email
ASC_OR_DESC >>> A
FILTER_CONDITION >>> null
TABLE_SCHEM >>> dbo
TABLE_CAT >>> tempdb
=====================================================
I've read through the following documentation link [JDBC doc]
Also, read through these stackoverflow questions on the same topic
[Question 1] [Question 2] [Question 3]
What should I do to get this done via JDBC? One option I have is to have DB specific queries to get the list of indices and run them through JDBC (but I don't want to go that route)
