How do I list the table names in a database?

Question

We have an Oracle 8i database on which I have only read access. We use ODBC and MS Access to read data from that database since we don't have Oracle Client software. This works fine. I am using ADO.NET with ASP.NET. Now I want to display a list of all the tables that I see in MS Access via ODBC. I have tried this with ODBC connection in C#. I am tried the following queries to get the list of tables, which did not work.

1. select table_name from dba_tables;
2. select table_name from all_tables;
3. select tname from tab;

Please help.

Thanks for the response. I tried them without luck. All I want to see is the same list of tables that are available in MS Access when I use ODBC to create Linked Tables.

This is the function that I am using to achieve this, which does not really work the way I would have liked.

public static ArrayList GetODBCTablesList()
        {
            try
            {                
                OdbcConnection DbConnection = new OdbcConnection("DSN=mydsn;UID=user1;PWD=pwd1;");
                DbConnection.Open();

                OdbcCommand DbCommand = DbConnection.CreateCommand();
                DbCommand.CommandText = "select table_name from all_tables";                
                OdbcDataReader DbReader = DbCommand.ExecuteReader();

                if (DbReader != null)
                {
                    ArrayList TableList = new ArrayList();
                    while (DbReader.Read())
                    {
                        TableList.Add(DbReader.GetString(0));
                    }
                    DbReader.Close();
                    DbCommand.Dispose();
                    DbConnection.Close();

                    TableList.Sort();
                    TableList.TrimToSize();
                    return TableList;
                }

                DbCommand.Dispose();
                DbConnection.Close();

                return null;
            }
            catch (Exception ex)
            {
                LogHandler.WriteLogMessage(ex.GetBaseException().ToString(), true);
                return null;
            }
        }

This gives me a list of tables which does not contain all the tables that I see when I link tables in MS Access using ODBC.

Solution 1

This works:

select table_name from tabs;

Solution 2

You can try select table_name from user_tables

or

select object_name from USER_objects where object_type='TABLE'

Solution 3

Since you're using ADO.NET, I would suggest to use OdbcConnection.GetSchema. This method returns a DataTable containing information about the schema of your database.

From this answer, this might work for you:

OdbcConnection.GetSchema("Tables")

Solution 4

Try:

SELECT owner, table_name
  FROM dba_tables

Taken from Get list of all tables in Oracle?

View more solutions

Comments

[Jeremy Goodell]

I read something about dba_tables being subject to permissions of some sort. But I don't recall the exact nature of that.