List all tables of a database

[Helge B. derhelgeqhotmail.com]

Hi there,

is it possible to list all the tables of a database with a SQL statement?

Thanks
H.

[DaveSatz]

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
--
HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------

<Helge B. derhelgeqhotmail.com> wrote in message
news:3ac4800b$1@news.devx.com...
>
> Hi there,
>
> is it possible to list all the tables of a database with a SQL statement?
>
> Thanks
> H.

[Mark]

As you can see from Dave's reponse this is database specific. Since you didn't
specify here is DB2's quick and easy - LIST TABLES FOR ALL. You can hit
the system tables too. This is not recommended in program because it can
cause problems when you go to a new version.

Mark


"DaveSatz" <davidsatz@yahoo.com> wrote:
>SELECT TABLE_NAME
>FROM INFORMATION_SCHEMA.TABLES
>WHERE TABLE_TYPE = 'BASE TABLE'
>ORDER BY TABLE_NAME
>--
>HTH,
>David Satz
>Principal Software Engineer
>Hyperion Solutions
>->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>
><Helge B. derhelgeqhotmail.com> wrote in message
>news:3ac4800b$1@news.devx.com...
>>
>> Hi there,
>>
>> is it possible to list all the tables of a database with a SQL statement?
>>
>> Thanks
>> H.
>
>

[Craig Clearman]

Helge,

>is it possible to list all the tables of a database with a SQL statement?

That depends entirely on the database.

In Oracle,

SELECT * FROM dba_tables

Of course, this assumes you have DBA permissions. If not, you can
still see all of the tables to which you have access, by issuing

SELECT * FROM all_tables

Dave gave you a good solution for SQL*Server.

Of course, if you want DB2, you have to do it differently again.

Ciao, Craig