how to query (oracle) database table structure in C# / (ADO?).NET 2.0

Question

I want to get table metadata for all table columns. Like type string(varchar2)/int/float/datetime and length for strings etc.

Cheers! -Matti

Answer 1

For all tables that you can access :

select * from all_tab_columns

For all tables in the current schema :

select * from user_tab_columns

This is specific to Oracle, but there is a more generic way to retrieve schema information : the DbConnection.GetSchema method :

schema_owner = "the_owner"; // or null
table_name = "the_table"; // or null
column_name = "the_column"; // or null
DataTable columns = connection.GetSchema("Columns", new string[] { schema_owner, table_name, column_name });

The resulting table contains all available column information that matches the criteria.

For a list of all available schema metadata, you can call GetSchema with DbMetaDataCollectionNames.MetaDataCollections as a parameter.

Answer 2

You can use the GetSchema method of the OracleConnection class.