ODBC.NET

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi;

I've looked and can't find this but I am guessing I just missed it somehow.
Using an ODBC.NET connection how can I:
1) Get a list of all tables in that database?
2) Get a list of all views in that database?
3) For a given table or view, get a list of all columns in that table/view?
4) Get a list of all stored procedures in that database?

I assume this is doable as ODBC is very mature. But I can't find a database
independent way to do this. (I did find it in OLEDB.NET).

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Ah, ADO.NET does expose a GetSchema from the Connection object--the question
is, does the Odbc.Net data provider implement it. Frankly, I doubt it.
That's the price of using an OSFA provider.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
 
I assume this is doable as ODBC is very mature. But I can't find a
database
independent way to do this. (I did find it in OLEDB.NET).
Click to expand...

It will depend on the ODBC driver for the particular RDBMS - which RDBMS is
it, AAMOI...?
 
Hi Dave,

The ODBC.NET itself support getting the schema information by using
OdbcConnection.GetSchema method. It can meet all the requirements here.
However, whether a database supports this depends on the database itself
and the driver that vendor has provided. In this case, we cannot say for
sure that we can do or cannot this. It is out of the scope that our app can
reach.

If anything is unclear, please feel free to let me know.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Hi;

Ok, I have it but with 2 things I would like to get better. First, to get
tables or views you can do:

using (OdbcConnection sc = new OdbcConnection(connStr))
{
sc.Open();
DataTable schemaTable = sc.GetSchema("tables"); // or views

for (int ind = 0; ind < schemaTable.Rows.Count; ind++)
if (((string)(schemaTable.Rows[ind])["TABLE_TYPE"]).ToUpper() == "TABLE")
System.Console.WriteLine(" " +
(string)(schemaTable.Rows[ind])["TABLE_NAME"]);
}

I tried using restrictions to get just non-system tables but everything I
tried gave me back no tables.

For stored procedures you can do:
using (OdbcConnection sc = new OdbcConnection(connStr))
{
sc.Open();
DataTable schemaTable = sc.GetSchema("procedures");

for (int ind = 0; ind < schemaTable.Rows.Count; ind++)
System.Console.WriteLine(" " +
(string)(schemaTable.Rows[ind])["PROCEDURE_NAME"]);
}

And for these I have found no way to get just user-defined ones. The
returned data has the same type for all of them.


Any ideas?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hi Dave,

1. The schema information on Tables only have 3 restrictions. They are
database, owner, and table name. So we cannot filter the user tables with
GetSchema method. In this case, the simplest way to to create a DataView
object on the schemaTable. Here is an example:

DataView dv = new DataView(schemaTable);
dv.RowFilter = "TABLE_TYPE='TABLE'";

Then dv will show user tables only.

2. The GetSchema("procedures") on SQL Server is actually calling
sp_stored_procedures to get all the procedures back. The returned table
from sp_stored_procedures didn't include an identifier to show if this
stored procedure is a system procedure or user one. In this case, we are
unable to know whether it is a system procedure.

It seems to be a limitation. Sorry for the inconvenience.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Hi Dave

The PROCEDURE_TYPE column is not used to indicate whether this procedure is
a user one or system one. This value can be one of the following:

0 = SQL_PT_UNKNOWN
1 = SQL_PT_PROCEDURE
2 = SQL_PT_FUNCTION

However, according to the document, SQL Server always returns 2.0.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Back
Top