getting list of all tables in MS SQL DB using OdbcConnection

[Guest]

Hi All,

I am very new to C# as well as to SQL. I need to write an application which
is connecting to SQL DB and getting all the user's tables and after create a
dataset for all the tables in the database and create an XML document with
all the info.

The problem is, that I can do this if I know the table name. However, what
if I don't know the table name nor I know how many user's tables in the
particular database.

Any idea how can I achive my goal?

Thank you in advance for any help.

Oleg

 

[Dave]

connecting to SQL DB and getting all the user's tables

If you are using Sql Server, then you can query a view called INFORMATION_SCHEMA:

SELECT * FROM INFORMATION_SCHEMA.TABLES;

The above query works in any database context. I believe there is a field named, "Type" with a value of "U" for all user tables.
This may only apply in sysobjects, but I'm not sure. Run the query and see what it returns.

create a dataset...

Use SqlDataAdapter and set it's SelectCommand property to a new SqlCommand instance. The new SqlCommand should have the Text
property set to the query above. Create a new SqlConnection object and assign it to the Connection property of the new SqlCommand
you have created.

(Sql... objects are found in System.Data.SqlClient namespace)

Create a new, empty DataSet instance (i.e. new DataSet()).

Invoke Adapter.Fill and pass in the new DataSet.

You will have a DataSet with all of the tables information in the current database.

create an XML document

On your new DataSet, call the Write method. This method has an overload that will write the complete DataSet to disk in Xml format
and optionally, along with it's schema.

GL