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.

select table_name from dba_tables;
select table_name from all_tables;
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.

Could you give us some more details on what is not working with query 2? — dpbradley, Sep 21 '10 at 20:03
Perhaps you should try them again, but this time, *with luck*. — Jeffrey Kemp, Sep 22 '10 at 04:30

Jeremy Goodell · Answer 1 · 2010-09-21T17:40:46.020

3

This works:

select table_name from tabs;

edited Sep 21 '10 at 17:40

answered Sep 21 '10 at 15:58

Jeremy Goodell

18,225
5
35
52

score 2 · Answer 2 · edited May 23 '17 at 12:31

2

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")

edited May 23 '17 at 12:31

Community

1
1

answered Mar 28 '14 at 18:20

bouvierr

3,563
3
27
32

score 2 · Answer 3 · answered Sep 21 '10 at 17:26

2

You can try select table_name from user_tables

or

select object_name from USER_objects where object_type='TABLE'

answered Sep 21 '10 at 17:26

Sathyajith Bhat

21,321
22
95
134

score 1 · Answer 4 · edited May 23 '17 at 12:13

1

Try:

SELECT owner, table_name
  FROM dba_tables

Taken from Get list of all tables in Oracle?

edited May 23 '17 at 12:13

Community

1
1

answered Sep 21 '10 at 19:26

Gage

7,365
9
47
77

I read something about dba_tables being subject to permissions of some sort. But I don't recall the exact nature of that. – Jeremy Goodell Sep 21 '10 at 20:16

How do I list the table names in a database?

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.

4 Answers4