MySQL: Get Tables?

PDTUM

Hello, I have installed the MySQL database program on my computer. It came with 2 databases installed and I created another called "Test" for experimentation. I can access all the information from WITHIN the proggram using the internal commands, however, I am looking to be able to access table information from an outside program, so I need to be able to get table information into that program (C#) to create queries. I can successfully list the names of the databases in the program, however, I have an issue getting the table names. I have used both of the following which are supposed to list the tables:

/*string Sql = "SHOW TABLES FROM " + thisDb;//WHERE thisDB is the selected database*/
/*string Sql = "Select * FROM information_schema.TABLES";*/

And I have tried several variations of the above. All seem to either return only the names of the 3 installed databases or nothing at all. Can anyone supply me with an SQL statement that will simply return the names of the tables inside a specifically defined database (thisDb) and, as long as I am on the topic, if your know this answer, how about another script to do the same thing for the table columns of each table. Thank you for your input and assistance....Pat

Lost User

This works for me:

USE thisDb;
SHOW TABLES;

loyal ginger

With ADO.NET, the DataReader's

GetSchemaTable()

method can be used to retrieve column information for a specific table. For example,

//suppose reader (of type MySqlDataReader) has been already created properly using a query like "select * from mytable;"

DataTable dt = reader.GetSchemaTable();

//get information for each column of the table
foreach (DataRow dr in dt.Rows) {
//retrieve each property of the column
foreach (DataColumn dc in dt.Columns) {
//just an example
Console.WriteLine(dc.ColumnName + " = " + dr[dc].ToString());
}
Console.WriteLine();
}

PDTUM

Thank You Shameel....once again you have bailed me out. I was actually going to email you with this question as I remember that we used to be able to do that here, but I could no longer find a link for it. In any event, and to help any others that are suffering along and who find this question in a search, I am enclosing the final code that I used for my application. And once again Shameel, I have marked your answer as correct. For other interested parties, the C# code is mine (simple and clean for better or worse), so don't blame Shameel for it...LOL. Best Regards, Pat

string thisDb = listViewDatabases.SelectedItems[0].Text;
string Sql = "Use " + thisDb + ";" + " Show Tables; ";

try
{
// Obtain the Tables in the Database
MySqlCommand Comm = new MySqlCommand(Sql, Conn);
MySqlDataAdapter da = new MySqlDataAdapter(Comm);
DataSet ds = new DataSet();
da.Fill(ds);

                int count = ds.Tables\[0\].Rows.Count;
                string\[\] tables = new string\[count\];

                for (int i = 0; i < count; i++)
                {
                    tables\[i\] = ds.Tables\[0\].Rows\[i\]\[0\].ToString();
                    listViewDbTables.Items.Add(tables\[i\].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error" + ex.Message, "Error");
            }

PDTUM

Thank you for your reply Ginger. I will add your code to my snippets for future use. The simple string from Shameel added to my own standard population method solved the problem for me. Thank you for your input and advice....Best Regards, Pat

Lost User

Anytime, glad to be of help :-)