Getting a list of Tables/Views/Procedures

[Abhishek Srivastava]

Hello All,

I am tring to get a list of Tables and Views that exist in
a DB instance of Oracle.

Or If I use SQL Server 2K then I want a list of all (user
owned, not those that are used by the db itself) Tables,
Views, Procedures that exist in a database.

How do I do this using C# and ado.net?

regards,
Abhishek.

[Joseph Albahari]

Hello Abhishek

> I am tring to get a list of Tables and Views that exist in
> a DB instance of Oracle.

select TABLE_NAME from USER_TABLES
select VIEW_NAME from USER_VIEWS

> Or If I use SQL Server 2K then I want a list of all (user
> owned, not those that are used by the db itself) Tables,
> Views, Procedures that exist in a database.

select type, user_name (uid) owner, object_name(id) object
from sysobjects
where type in ('U', 'V', 'P')
and ObjectProperty (id, N'IsMSShipped') = 0

Regards

Joseph

[Abhishek Srivastava]

Thanks for the code snippet.

The problem is that this code will work only when the DB
is Sql Server.

Does the API of ADO.NET provide a standard way of
extracting the meta data about a DB (something what the
java.sql.DatabaseMetaData class provides in Java).

That way..., my meta data extraction code will work with
all OleDb Data Sources and not just SQL server.

regards,
Abhishek.

>.
>

[Joseph Albahari]

You can get at *some* of the metadata in a generic manner (eg column names &
properties, using DataAdapter.FillSchema or a DataReader object), but
ADO.NET does not allow you to go much further. You cannot enumerate the
available databases/catalogs or tables, stored procedures or other objects.
This is a shame because I think both OLE-DB and ODBC specifications provide
this.

Joseph

"Abhishek Srivastava" <abhi...@hotmail.com> wrote in message
news:149bb01c127f8$bc96d9e0$b1e62ecf@tkmsftngxa04...

[Frédéric Requière]

ADO.NET provides other objects than previous version of
ADO.

So to connect a SQL server, you do not use ADO.Connection
but a SQLCLient/sqlConnection and for retreiving data, you
will use SELECT statement (ie) with a command, so
SqlClient/sqlCommand

The object models are provided in the help, but here is a
sample code in c#:

System.Data.SqlClient.SqlConnection connection;
System.Data.SqlClient.SqlCommand command;
System.Data.SqlClient.SqlDataAdapter adapter;
System.Data.DataSet dataSet;

connection = new System.Data.SqlClient.SqlConnection
(connectionStringToSQLServer);
command = new System.Data.SqlClient.SqlCommand();
adapter = new System.Data.SqlClient.SqlDataAdapter();
dataSet = new DataSet(name);
command.Connection = connection;
command.CommandText = "SELECT * FROM ...";
adapter.SelectCommand = command;
connection.Open();
adapter.Fill(dataSet,dstTable);
connection.Close();

Then you have all your data in dataSet.
You need to read its object model, but it is quite simple.
Just rows, columns, and transtyping values...

Cheers!

Fred.

>.
>

[Jeff Reese]

Take a look at the OleDbConnection.GetOleDbSchemaTable method. It should do
what you want.

--
Jeff Reese
http://www.codebehind.net/

"Abhishek Srivastava" <abhi...@hotmail.com> wrote in message
news:149bb01c127f8$bc96d9e0$b1e62ecf@tkmsftngxa04...

[Hussein Abuthuraya(MSFT)]

Hi Abhishek

Yes, Jeff is correct. The GetOleDbSchemaTable method of the
OleDbConnection object is the key to all the Schema Info for any DataSource.

Here is a code snippet that uses the Microsoft OLEDB provider for Oracle:

Dim conn As OleDbConnection = New
OleDbConnection("Provider=MSDAORA.1;Password=xxx;User ID=xxx;Data
Source=oracle816")
conn.Open()

Dim dt As DataTable
Dim rst As Object
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, rst)

where dt is a table that will have all the details about the procedures
that available in the Oracle datasource. Please review the documentation
on the different OleDbSchemaGuid option. This info is found in the folling
link:

ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfsystemdataoledboledbschemaguidme
mberstopic.htm

I hope this helps.

Thanks,
Hussein Abuthuraya
Microsoft Visual Studio .NET Readiness Team