Get The DataBase Table Name in C#

Question

Alright, I know that this case has been discussed all over the web, but I still don't understand.

Here's my search result so far:

"SELECT table_name AS Name FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'";

In this SQL code, I thought that the INFORMATION_SCHEMA was a database name and when I executed the code (of course with the connected database name), an exception was thrown saying that the database name doesn't exist.

Updated:

When I run the SQL query and execute it with:

OleDbDataReader reader = cmd.ExecuteReader();

It says: Could not find: path/INFORMATION_SCHEMA.mdb;

The things I already done and they're good are:

The connection string is filled with the database name.
I can do the usual SQL CommandText to my database.
I'm using the OLEDB DBMS.

SOLVED

And my final code with help of other people is:

DataTable MySchemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" });

for (int i = 0; i < MySchemaTable.Rows.Count; i++) {
combobox1.Items.Add(MySchemaTable.Rows[i].ItemArray[2].ToString()); }

Another search is this:

`DataTable T = con.GetSchema("Tables");`

And when I print out the t.ToString();, it's only showwing the Tables.

And many more searches.

CASE CLOSED!

@CloudyMarble: right now im using OLEDB, the error message is path didnt exist whether im using INFORMATION_SCHEMA line or not — Geofff, Dec 11 '13 at 11:04
Please add the OLEDB Tag, Its very confusing since the term INFORMATION_SCHEMA exists in more than one DBMS — CloudyMarble, Dec 11 '13 at 11:28

score 0 · Answer 1 · answered Dec 11 '13 at 10:53

0

INFORMATION_SCHEMA is not a database name, it is a schema that exists in every database. It contains views that give you information about the database structure.

An information schema view is one of several methods SQL Server provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

Your query will give you the names of all tables and in your connection string you should specify the database name for which you want to get all tables.

More about INFORMATION_SCHEMA on MSDN.

answered Dec 11 '13 at 10:53

Szymon

42,577
16
96
114

It is a DB Name in MySQL – CloudyMarble Dec 11 '13 at 10:55
Is it MySQL? I thought SQL Server as it actually works in SQL Server. – Szymon Dec 11 '13 at 10:56
I thought its MySQL, but im not sure now, its somehow unclear. – CloudyMarble Dec 11 '13 at 10:57
it's not MySQL but it's look SqlServer ;-) – Mehdi Bugnard Dec 11 '13 at 10:57
hm thats weird, i got this Error Messages when i write INFORMATION_SCHEMA, see here "Could not find path\INFORMATION_SCHEMA.mdb" so i thought that is a database – Geofff Dec 11 '13 at 11:03
@Geofff Don't put INFORMATION_SCHEMA into your database name in the connection string. Put the normal database name. – Szymon Dec 11 '13 at 11:06
@Szymon yeah i put the normal database name sir in the connection string – Geofff Dec 11 '13 at 11:10

score 0 · Answer 2 · answered Dec 11 '13 at 10:55

0

Do you can to try to use the database before you start your query ? Like this :

USE [databaseName]
GO
SELECT table_name AS Name FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'

So you can be sure , you are connected to the good database before start the query

answered Dec 11 '13 at 10:55

Mehdi Bugnard

3,889
4
45
86

where databaseName with .mdf in behind string pardon? – Geofff Dec 11 '13 at 11:08

score 0 · Accepted Answer · answered Dec 11 '13 at 11:27

0

You can not use the INFORMATION_SCHEMA in OLEDB, it has a different way to provide its table information. Take a look at GetOleDbSchemaTable. It looks like:

MySchemaTable = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                   New Object() {Nothing, Nothing, Nothing, "TABLE"})

The Documentation states here:

The .NET Framework Data Provider for OLE DB also exposes schema information by using the GetOleDbSchemaTable method of the OleDbConnection object. As arguments, GetOleDbSchemaTable takes an OleDbSchemaGuid that identifies the schema information to return, and an array of restrictions on those returned columns. GetOleDbSchemaTable returns a DataTable populated with the requested schema information.

answered Dec 11 '13 at 11:27

CloudyMarble

36,908
70
97
130

where MySchemaTable as? – Geofff Dec 11 '13 at 11:31
If you would click on the link and see the documentation you would find its a DataTable. – CloudyMarble Dec 11 '13 at 11:34
@Geofff Glad for you but you should not change your original question to include the right solution but accept an answer which answers your question. – CloudyMarble Dec 11 '13 at 12:18

score 0 · Answer 4 · edited May 23 '17 at 12:25

No Need for all those troubles. You can just follow this answer

To check if a table exists you can extend DbConnection like this:
public static class DbConnectionExtensions
{
    public static bool TableExists(this DbConnection conn, string table)
    {
        conn.open();
        var exists = conn.GetSchema("Tables", new string[4] { null, null, table, "TABLE" }).Rows.Count > 0;
        conn.close();
        return exists;
    }
}
Then you can call TableExists in any derived class like OleDbConnection, SQLiteConnection or SqlConnection.

Get The DataBase Table Name in C#

4 Answers4