How to get all table names from a database?

I'd like to retrieve all table names from a database schema, and, if possible, get all table starting with a specified prefix.

I tried using JDBC's connection.getMetaData().getTables() but it didn't work at all.

Connection jdbcConnection = DriverManager.getConnection("", "", "");
DatabaseMetaData m = jdbcConnection.getMetaData();
ResultSet tables = m.getTables(jdbcConnection.getCatalog(), null, "TAB_%", null);
for (int i = 0; i < tables.getMetaData().getColumnCount(); i++) {
   System.out.println("table = " + tables.getMetaData().getTableName(i));
}

Could someone help me on this?

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

This is an example from java2s.com:

DatabaseMetaData md = conn.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
  System.out.println(rs.getString(3));
}

Column 3 is the TABLE_NAME (see documentation of DatabaseMetaData::getTables).

Get all table names of a particular database by SQL query?, Catalog tables, including systables itself, have a row in systables as do views, synonyms, or dba_tables to find all the names of tables present in the database . I want to get tables' names of a particular database using a general query which should suitable for all database types. I have tried following: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' But it is giving table names of all databases of a particular server but I want to get tables names of selected database only.

 public void getDatabaseMetaData()
    {
        try {

            DatabaseMetaData dbmd = conn.getMetaData();
            String[] types = {"TABLE"};
            ResultSet rs = dbmd.getTables(null, null, "%", types);
            while (rs.next()) {
                System.out.println(rs.getString("TABLE_NAME"));
            }
        } 
            catch (SQLException e) {
            e.printStackTrace();
        }
    }

What is the SQL query to find all table names present in a database , SQL command to list all tables in Oracle. 1) Show all tables owned by the current user: SELECT table_name FROM user_tables; 2) Show all tables in the current database: SELECT table_name FROM dba_tables; 3) Show all tables that are accessible by the current user: Write the following query. SELECT*FROMinformation_schema.tables. Wel, there is one more way by which we can list all the tables in a database. Write the following query in the query analyzer. SELECT sobjects.name FROM sysobjectssobjects WHERE sobjects.xtype='U'. SQL Server.

In your example problem is passed table name pattern in getTables function of DatabaseMetaData.

Some database supports Uppercase identifier, some support lower case identifiers. For example oracle fetches the table name in upper case, while postgreSQL fetch it in lower case.

DatabaseMetaDeta provides a method to determine how the database stores identifiers, can be mixed case, uppercase, lowercase see:http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#storesMixedCaseIdentifiers()

From below example, you can get all tables and view of providing table name pattern, if you want only tables then remove "VIEW" from TYPES array.

public class DBUtility {
    private static final String[] TYPES = {"TABLE", "VIEW"};
    public static void getTableMetadata(Connection jdbcConnection, String tableNamePattern, String schema, String catalog, boolean isQuoted) throws HibernateException {
            try {
                DatabaseMetaData meta = jdbcConnection.getMetaData();
                ResultSet rs = null;
                try {
                    if ( (isQuoted && meta.storesMixedCaseQuotedIdentifiers())) {
                        rs = meta.getTables(catalog, schema, tableNamePattern, TYPES);
                    } else if ( (isQuoted && meta.storesUpperCaseQuotedIdentifiers())
                        || (!isQuoted && meta.storesUpperCaseIdentifiers() )) {
                        rs = meta.getTables(
                                StringHelper.toUpperCase(catalog),
                                StringHelper.toUpperCase(schema),
                                StringHelper.toUpperCase(tableNamePattern),
                                TYPES
                            );
                    }
                    else if ( (isQuoted && meta.storesLowerCaseQuotedIdentifiers())
                            || (!isQuoted && meta.storesLowerCaseIdentifiers() )) {
                        rs = meta.getTables( 
                                StringHelper.toLowerCase( catalog ),
                                StringHelper.toLowerCase(schema), 
                                StringHelper.toLowerCase(tableNamePattern), 
                                TYPES 
                            );
                    }
                    else {
                        rs = meta.getTables(catalog, schema, tableNamePattern, TYPES);
                    }

                    while ( rs.next() ) {
                        String tableName = rs.getString("TABLE_NAME");
                        System.out.println("table = " + tableName);
                    }



                }
                finally {
                    if (rs!=null) rs.close();
                }
            }
            catch (SQLException sqlException) {
                // TODO 
                sqlException.printStackTrace();
            }

    }

    public static void main(String[] args) {
        Connection jdbcConnection;
        try {
            jdbcConnection = DriverManager.getConnection("", "", "");
            getTableMetadata(jdbcConnection, "tbl%", null, null, false);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

SQL List All tables, SYS.SYSOBJECTS contains a row for every object that has been created in the database, including stored procedures , views , and user tables� To list all tables in MySQL, first, you connect to the MySQL database server using the following command: mysql -u username -p MySQL then prompts for the password; just enter the correct one for the user and press enter.

If you want to use a high-level API, that hides a lot of the JDBC complexity around database schema metadata, take a look at this article: http://www.devx.com/Java/Article/32443/1954

SQL Server List Tables: How to Show All Tables, In this SQL tutorial, we will see examples of getting names of all tables from MySQL and the SQL Server database. In MySQL, there are two ways to find names of� List all Tables in Oracle database, accessed by Current user: The below query lists all tables in oracle which are accessible by the current user. SELECT owner, table_name FROM all_tables; The owner column displays the owner’s name of the table. user_tables table does not have the owner column.

public static ArrayList<String> getTablesList(Connection conn)
            throws SQLException {

        ArrayList<String> listofTable = new ArrayList<String>();

        DatabaseMetaData md = conn.getMetaData();

        ResultSet rs = md.getTables(null, null, "%", null);

        while (rs.next()) {
            if (rs.getString(4).equalsIgnoreCase("TABLE")) {
                listofTable.add(rs.getString(3));
            }
        }
        return listofTable;
    }

SQL Query to Find All Table Names on a Database , Get documentation of your databases. Generate convenient documentation of your databases in minutes and share it with your team. Capture and preserve� Hello all- I am seeking to get all the table names from a SQL database. I am been searching on the web but have not found an adequate answer- So far I have: libname X oledb provider=sqloledb dsn='SQLXXXDR' properties=("Integrated Security"=SSPI "Persist Security Info"=True "Initial Catalog"=medX)

List all table names in particular schema in SQL Server, TSQL: How To Get All Table Names In A Database. 9,848 views9.8K views. • Feb 12, 2014. 14 5 Duration: 2:40 Posted: Feb 12, 2014 This query returns a complete list of tables: SELECT owner, table_name FROM dba_tables. In case you can't access this view, try this: SELECT owner, table_name FROM all_tables. If it still doesn't work, try this, but note that this will return a list of table owned by the current user, not all tables in the database: SELECT table_name FROM user_tables

TSQL: How To Get All Table Names In A Database, You can get the list of tables in the current database in MySQL using the SHOW TABLES query.Show tables;Following JDBC program retrieves� In this SQL Server example, we are using INFORMATION_SCHEMA.COLUMNS to get the table names where the column name is equal to Education. -- Query to Find all Tables that Contain Column Name USE [SQL Tutorial] GO SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like '%Education%' ORDER BY TABLE_NAME.

How to get all table names from a database using JDBC?, Syntax (When we have only single database): Select * from schema_name.table_name. Syntax (When we have multiple databases): Select * from database_name.schema_name.table_name. Example: WHERE. INFORMATION_SCHEMA views allow you to retrieve metadata about the objects within a database. INFORMATION_SCHEMA. Output: The syntax provided in this article works only for SQL Server and MySQL. If you want to know how many tables are present in your database and the details of the table like TABLE_SCHEMA, TABLE_TYPE and all. Syntax (When we have only single database): Select * from schema_name.table_name

Comments
  • If this fails for you (like it did me) you need to make sure the database user can actually show tables on the database.
  • It shows all tables in all databases. You can pass the database name as the first parameter to shows tables only in that db.
  • For mysql, ResultSet rs = md.getTables(connection.getCatalog(), null, "%", new String [] {"TABLE"}); gives you only tables of your current schema.
  • Why are we giving a % here? Shouldn't we use something like ".*"?
  • @Pardhu: This is not a regex, % is used as wildcard in SQL.
  • It shows all tables in all databases. You can pass the database name as the first parameter to shows tables only in that db.