Enumerating Database tables programmatically in SQLServer or oracle

[Cless]

Hi everyone,

Given a connection string, is there a way to retrieve all database
objects from a database. I already know through the MSDN documentation
of .NET of how to enumerate all database instances of sqlserver 2000
and up from a local network, but unfortunately I am yet to find an
example of how to get all tables from a given database.


Regards,

cless

 

[Lasse Vågsæther Karlsen]

Hi everyone,

Given a connection string, is there a way to retrieve all database
objects from a database. I already know through the MSDN documentation
of .NET of how to enumerate all database instances of sqlserver 2000
and up from a local network, but unfortunately I am yet to find an
example of how to get all tables from a given database.


Regards,

cless

Well, you typically issue queries against system schema tables in the
database, which contain information about which user tables are present.

The names and contents of those tables tends to vary from one database
engine vendor to the next, so you might not be able to write one piece
of code that works on all types of connection.

In SQL Server 2005, look at the INFORMATION_SCHEMA schema and see what
it contains.

 

[Arne Vajhøj]

Given a connection string, is there a way to retrieve all database
objects from a database. I already know through the MSDN documentation
of .NET of how to enumerate all database instances of sqlserver 2000
and up from a local network, but unfortunately I am yet to find an
example of how to get all tables from a given database.

There are lots of ways but none that will work with every database.

Databases that have decent ANSI SQL support:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_SCHEMA='xxxx' AND TABLE_TYPE='Base table'

OLE DB:

OleDbConnection GetOleDbSchemaTable

Database specific commands:

SQLServer:

SP_TABLES

MySQL:

SHOW TABLES

Arne

 

[Cless]

Thank you very much for your reply.