Select all table names from Oracle DB

Question

I write a program which scans all tablenames of a database and displays all

My Db has the Tables : User, Order,History

It should look like this:" Existing Tables: User Order History"

how should the command look like?

string SqlOrder="Select ??? from TestDB"

You need to fire the statement, get the results then format these in c# — CloudyMarble, Mar 06 '13 at 09:28

score 3 · Accepted Answer · edited Mar 06 '13 at 10:03

3

Try this

SELECT 'Existing Tables: ' || wm_concat(table_name) tablenames 
  FROM user_tables;

For the sample Oracle HR database it returns

TABLENAMES
------------------------------------------------------------------------------------
Existing Tables: REGIONS,LOCATIONS,DEPARTMENTS,JOBS,EMPLOYEES,JOB_HISTORY,COUNTRIES

UPDATE: Example with LISTAGG()

SELECT 'Existing Tables: ' || LISTAGG(table_name, ',') 
        WITHIN GROUP (ORDER BY table_name) tablenames 
  FROM user_tables;

edited Mar 06 '13 at 10:03

a_horse_with_no_name

497,550
91
775
843

answered Mar 06 '13 at 09:36

peterm

88,818
14
143
153

LISTAGG() could be relevant too : http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php#listagg – CloudyMarble Mar 06 '13 at 09:43
Agree. Added to the answer. – peterm Mar 06 '13 at 09:53
This answere do exactly what i want to do thx ! – Sam Mar 06 '13 at 10:31

score 1 · Answer 2 · answered Mar 06 '13 at 09:28

1

select table_name
from all_tables

More details in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_2117.htm#i1592091

answered Mar 06 '13 at 09:28

a_horse_with_no_name

497,550
91
775
843

manual link is missing, besides i guess the OP didnt mean this – CloudyMarble Mar 06 '13 at 09:29
I understood it like he would like to have the string before the tablenames and the tablenames as coma separated which would explain tagging the question with the C# tag. – CloudyMarble Mar 06 '13 at 09:32
@TwoMore: but he still neeeds to select all table names somehow. – a_horse_with_no_name Mar 06 '13 at 09:33
absolutly right, i have no problem with your answer but wonders if it answers the question, guess we will have to wait for the OP – CloudyMarble Mar 06 '13 at 09:35
thx that helps a lot. – Sam Mar 06 '13 at 10:29

Select all table names from Oracle DB

2 Answers2