Get Table and Field Names

  • Thread starter Thread starter et
  • Start date Start date
E

et

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.
 
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
 
I imagine after you have the tablenames you could just iterate through
each table's DataColumnCollection object to get their names.
 
et said:
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?
Click to expand...

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,
 
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)
 
Back
Top