Get Table and Field Names

How do I get the names of the tables in a database via ado.net? Once I have
the table names, how do I get the names of the Fields for each table? I
used to use OpenSchema in vb6 but do not find a correlating command for
.net. Thanks.

John
Sat Nov 13 16:12:48 CST 2004

If you use the OleDbConnection.GetOleDbSchemaTable method you can get a
number of pieces of information from the database. I don't know what
type of database you are connecting to so your experience may vary on
how well this works with it, but I have used it with success with a
couple of different database types. Hope this helps you out.

Have A Better One!

John M Deal, MCP
Necessity Software

et wrote:
> How do I get the names of the tables in a database via ado.net? Once I have
> the table names, how do I get the names of the Fields for each table? I
> used to use OpenSchema in vb6 but do not find a correlating command for
> .net. Thanks.
>
>

JuLiE
Sun Nov 14 23:20:57 CST 2004


I imagine after you have the tablenames you could just iterate through
each table's DataColumnCollection object to get their names.

On Sat, 13 Nov 2004 14:23:23 -0700, "et" <eagletender2001@yahoo.com>
wrote:

>How do I get the names of the tables in a database via ado.net? Once I have
>the table names, how do I get the names of the Fields for each table? I
>used to use OpenSchema in vb6 but do not find a correlating command for
>.net. Thanks.
>

Oenone
Mon Nov 15 09:32:11 CST 2004

et wrote:
> How do I get the names of the tables in a database via ado.net? Once
> I have the table names, how do I get the names of the Fields for each
> table?

I've been writing code to do exactly this over the last few days. As has
already been mentioned, the solution depends in part upon which database
objects you're using.

If you're using the SqlClient objects, you can execute the following SQL
statement to get the tables:

SELECT * FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'

Note however that this will also return the "dtproperties" table, which you
probably don't want so you can modify the query as follows to work around
this:

SELECT * FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(table_name),'IsMSShipped')=0

If you're using OleDbClient objects, you can use this to get the tables:

Dim dt as DataTable
dt = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New String()
{Nothing, Nothing, Nothing, "TABLE"})

In both cases, to get the list of fields I used the FillSchema method of the
DataAdapter object. This returns all the column information for a query
(SELECT * FROM TABLE) so that it can be interrogated using the Columns
collection of the appropriate Table object within the DataSet.

Hope that helps,

--

(O)enone

Paschalis
Tue Nov 16 07:58:56 CST 2004

You can also get table and field names using a stored
procedure.

To get a list of available tables in current database,
execute the following command:
EXEC sp_msforeachtable @command1='print ''?'''

or

EXEC sp_msforeachtable @command1='print REPLACE(REPLACE
(''?'', ''[dbo].['', ''''), '']'', '''')'

Additionally, to retrieve information about extended field
properties (like descriptions) for a table, execute the
following statement:
select * from ::fn_listextendedproperty
(null, 'user', 'dbo', 'table', '<table_name>', 'column',
default)

where <table_name> is the table name.


>-----Original Message-----
>How do I get the names of the tables in a database via
ado.net? Once I have
>the table names, how do I get the names of the Fields for
each table? I
>used to use OpenSchema in vb6 but do not find a
correlating command for
>..net. Thanks.
>
>
>.
>